Tag Archives: Microsoft

Unknown's avatar Chris 8:31 pm on August 19, 2025
Tags: , database, Microsoft, ,   

Troubleshooting High SQL Server CPU on Amazon RDS

Today’s scenario consists of a db.m6i.2xlarge Amazon RDS instance running Microsoft SQL Server Web Edition. A newer “greenfield” web application has a larger number of users hitting it during work hours.

An alert indicated that the server was nearing its CPU capacity. To start our investigation, we examine the instance’s Monitoring tab and see that the “CPUUtilization” metric indeed indicates an issue. This chart shows a server restart around the 17:35 mark to free up some resources. However, it quickly rockets back up.

Next, we examine our Database Insights (which you will need to activate in advance). Here we see CPU waits exceeding our max vCPU threshold, indicating an issue.

We then scroll down to the “Top SQL” tab and see that our most considerable wait (taking up 5.44% of the database load at the time of this screenshot, a few hours after the fix) is our target SQL query.

One of the items that we double-checked and found as an oversight was that the max degrees of parallelism (MAXDOP) parameter was set to 0 (unlimited). When using OLTP, or Online Transaction Processing, it’s best to set it to 1, effectively disabling parallelism. It ensures that no single query can consume too many resources, leaving processor cores available for other user requests. This improves overall system responsiveness and throughput.

However, after setting this to 1 and rebooting the instance, we found the primary issue remained. MAXDOP issues tend to be reflected as “CXPACKET” waits instead of “CPU” waits, and you can see all the “CXPACKET” waits, shown in light blue in the screenshot chart below, ceased at 18:30 when the parameter was changed from 0 to 1 and the server was emergency restarted.

During troubleshooting, developers optimized the query to join on a primary ID and then do a fuzzy search. Per the developer, the previous query was not utilizing the indexes properly.

At about 19:30 on the chart, the optimized query was pushed to production. The SQL Server was then emergency restarted to clear the hanging queries. You can see that the issue was then resolved with a practical database load and wait metric.

Random monitoring for a few hours confirmed that the fixes have resolved the issue.

In conclusion, utilize AWS RDS’s database insights and CloudWatch monitoring tools to look for clues. Often, on an OLTP stack, database waits become an issue that manifests as slow-loading web pages. Use application performance monitors (APM) such as FusionReactor for ColdFusion and Lucee servers to help you narrow down troublesome issues.

As a side note, while restarting production databases during normal hours is never great, you have to weigh the benefits. In this case a minute or two of downtime won over endless lag. Credit goes to Amazon RDS for a very speedy server restart. We were back up within a minute or two at most after hitting restart!

#aws, #database, #microsoft, #rds, #sql

Unknown's avatar Chris 12:53 pm on August 28, 2017
Tags: , Export, Import, Microsoft, ,   

Copying MS SQL 2016 Database on AWS RDS

A common task on a SQL Server might be to copy a database into a new database. This could be on the same server or a different server.

On Amazon Web Service’s (AWS) Relational Database Service (RDS) this task becomes a little more complex to plan and execute. There are a few reasons for this:

  1. You don’t have access to the local file system outside of creating a database in the defaulted path.
  2. You do not have the needed permissions to run “Copy Database Wizard”
  3. Doing a “native” backup/restore using S3 would create a duplicate “family_guid” which is impossible to restore on your RDS instance without deleting the source database.

If you use MS SQL 2016 you can export/import a “Data-tier Application” which is stored as a “.bacpac” file. This is a schema and data native format package (not human readable). In reality it’s a .zip file, so if you open it in something like 7-Zip you can see the package contents. This package is going to be lacking items such as the physical index (the index configuration is intact) and the transaction logs. Therefore it tends to be smaller than an actual native backup.

Keep in mind all data is transmitted from the source machine (AWS RDS) to your workstation. If this is a larger database, you may wish to consider doing this from an EC2 instance to create a faster connection, provide for a larger drive workspace and potentially save on network costs.

Here are the steps to take to backup and then restore a “.bacpac” file. This example is done using AWS RDS, but the same steps would be taken for about any environment including local.

  1. Open Microsoft SQL Server Managment Studio (MSSMS) 2016 or better and connect to your database server.
  2. Right click the source database, go to tasks and “Export Data-tier Application”.
    1
  3. Press “Next” to go to “Export Settings”. Enter a path on your local workstation to save the package to and press “Next”.
    2
  4. After you press “Finish”, it will then begin the copy process from the source machine to your local machine.
    3
  5. If you wish to import the database to another server, connect to it now via the MSSMS.
  6. Right-click the “Databases” group object and select “Import Data-tier Application”. Press “Next”.
    4
  7. Specify the path on your local machine to the “.bacpac” file being imported. Then press “Next”.
  8. Specify a new database name. It can not be the name of an existing database. Press “Next” and then “Finish”.
    5
  9. The new database will be created and imported.
    6

Update 10/29/2018
It appears the “family_guid” issue is no longer an issue. I have not verified it as of yet. See https://aws.amazon.com/about-aws/whats-new/2018/10/amazon-rds-for-sql-server-enhances-backup-and-restore-capabilities/

#aws, #export, #import, #microsoft, #rds, #sql

Unknown's avatar Chris 4:49 pm on October 16, 2014
Tags: Blue Screen of Death, Microsoft, Registry, Update, , Windows 2008 Server   

Manual Windows 2008 Registry Restore

After a Windows Update the lovely “Blue Screen of Death” appeared on one of our servers. Frantic to find a solution, “Boot to the last known working configuration” wasn’t working. A system restore was a last resort option.

Here’s what the error consisted of:

STOP: c0000218 {Registry File Failure}
The registry cannot load the hive (file):
\Systemroot\System32\Config\SOFTWARE
or its log or alternate.
It is corrupt, absent, or not writable.

To resolve the issue I:

  1. Boot to the Windows 2008 Server Install DVD
  2. Click “Repair Computer” on the second screen
  3. Open a command prompt on the second or third prompt
  4. Change directory to C:\Windows\System32\Config\
  5. Rename “SOFTWARE” to “SOFTWARE.BAK”
  6. Copy “RegBack\SOFTWARE” to that directory
  7. Reboot

This restored the SOFTWARE registry to its previous state before the Windows Update. I then had a pending list of Windows Updates to install again. But I’ll leave that for another day for now to see if anyone else is having issues.

#blue-screen-of-death, #microsoft, #registry, #update, #windows, #windows-2008-server

Unknown's avatar Chris 7:56 pm on March 22, 2011
Tags: CDN, , , Microsoft, plugin,   

jQuery Templates

I decided to dig into the new official jQuery Template Plugin developed from Microsoft yesterday. After I finally got the darn thing to work it makes inserting formatted HTML populated with data way easier.

After doing some searches on Google I kept finding articles that announce that Templates would be built into the version 1.5 core. I continued down the path coding by example and researching the API Docs. However I kept running into “tmpl is not a function”. After some continued research I finally found a tiny little comment made by John Resig that it in fact did not make it into the 1.5 release. So now that error makes sense.

To resolve this issue you must still load the jQuery plugin. It appears the plugin is still in beta stages and is available for download from Github or the Microsoft CDN.

Before I’ve been unable to use Microsoft’s CDN because it did not have SSL. But I went ahead and tested to see if they now have it included, and they do!

Here’s how I use it:

<script src="//ajax.microsoft.com/ajax/jquery.templates/beta1/jquery.tmpl.min.js"></script>

This will automatically call it via http or https depending upon the current browser protocol being used.

See a related blog for jQuery on Google’s CDN here.

#cdn, #google, #jquery, #microsoft, #plugin, #template