SQL Performance tuning - Drilling into the problem

By -
Example 1 – A query is slow because the response time is greater than 10 seconds and normally it’s 2 seconds
Example 2  -  Client User requests are queing up and we notice CPU usage at 100% , whereas the normal level is 25%
In Example 1, we’re using the response time  as the unit of measure for the task i.e query
In Example 2 , CPU usage is the unit of measure for the client user requests
Once the problem is quantified in this manner , break the quantities down into smaller parts. It may be session IDs (SPID), execution plan data etc. Each profile has it’s own data requirements. The key message is : attempt to gather performance data matching the problem
Creating the profile at the correct level of detail requires relevant data. SQL Server offers different methods of collecting data e.g DMV, Traces , Custom queries.   It’s important to collect data at the right range. If analysing a problem at a user level , then collect data at the user level.  Strip out  noise.
If analysing a user session resource usage , don’t use  system wide aggregates as your starting point. The main point is to collect relevant data.
At a user level, SQL Server is a collection of sessions (SPID). Nothing occurs without a session. Sessions are used to execute T-SQL statements. During execution of a  T-SQL statement , there is work on the CPU or the session goes idle – either being blocked or waiting for some activity to complete. In other words, different events are contributing to the overall time spent
Identify the SPID of the user complaint. Break the session down and analyse how the time is spent during the session. How is time spent during the session? IO, CPU, Locks , idle :  all use time and contribute to the response time.
 A chronologically ordered breakdown of how the session spends time gives clues for the delays. The delay could be on the network , application , storage etc
 So far , we’ve looked at the user level. What happens  if insufficient data is available ? At this point the DBA collects more data , waiting for a reoccurrence.
Developing a systematic and objective approach to SQL performance troubleshooting is key to fixing problems. Avoid relying on luck and focus on the facts.

Comments

Post a Comment

Popular posts from this blog

Index Clean-Up Scripts

By -
dropping un-used ones, adding missing ones, and fine-tuning the ones I already have. I thought I’d share some of the scripts I’ve been using to accomplish this. Un-Used Indexes Script Declare @dbid int     , @dbName varchar(100);   Select @dbid = DB_ID()     , @dbName = DB_Name();   With partitionCTE (object_id, index_id, row_count, partition_count)  As (     Select [object_id]         , index_id         , Sum([rows]) As 'row_count'         , Count(partition_id) As 'partition_count'     From sys.partitions     Group By [object_id]         , index_id )    Select Object_Name(i.[object_id]) as objectName         , i.name         , Case              When i.is_unique = 1          ...
Read more

forgot sa password and no logins are added

By -
Image
Stop the SQL services Edit the properties of the SQL Service Change the startup parameters of the SQL service by adding a  –m;  in front of the existing parameters Start the SQL services. These are now running in Single User Mode. Start CMD on tthe SQL server Start the SQLCMD command. Now we create a new user. Enter following commands CREATE LOGIN recovery WITH PASSWORD = ‘TopSecret 1′ (Remember SQL server has default strong password policy Go Now this user is created Now we grant the user a SYSADMIN roles using the same SQLCMD window. sp_addsrvrolemember ‘recovery’, ‘sysadmin’ go
Read more

The SQL Server DBA’s Guide to Teradata

By -
Being a long-time devotee of all things Microsoft SQL Server, I had quite a bit of learning to do to get up to speed on our new Teradata appliance. This guide attempts to distill some of what I have learned. I will primarily focus on what I have found most lacking: examples of how to convert SQL Server T-SQL to Teradata SQL. This guide will use the vendor-supplied sample databases:  SQL Server’s AdventureWorksDW2012 , and the  Teradata’s Express 14.0 Retail database . Also, while this is probably self explanatory, the “mufford” object referenced in the Teradata examples is simply my own personal sandbox. You’ll need to replace any “mufford” reference to whatever your development sandbox may be named. Release History: 2012-08-17 – Added UPDATE examples; reformatted 2012-08-12 – Added date examples 2012-08-08 – Initial Release SELECT Statements Simple SELECT SQL Server: Best practice is to qualify the table schema; qualifying the database is optional unless ...
Read more