SQL Server performance problem, how bad it is or rather I ask how good are you to find the root cause?
I always trust how good your resources are to find out a root cause than how bad the problem is?
It is one of the best point of disaster recovery position I wanted to be to get to work on solution on quicker basis than worrying about the impact, the business will have their say about availability or scalability for their application! Well we cannot work on both of the requirements at the same time and anytime a DBA must be hands-on to solve the problem in a flexible manner than in panic.
Lately, it has been much of Architecture and Solution design tasks for me rather than working on a real-time performance problem and honest opinion is I miss much of those good old 'DBA' days. One of the best features that I consider in SQL Server 2008 version is 'Multiserver Administration' taking the help of SQL Agent that takes advantage of the self-tuning features of SQL Server, does it work that way! But you should rather concern on normal conditions of the platform and additional job tuning is not necessary. In any case the performance between client and server suffers if the network is not good enough and loads increase when you run jobs, generate alerts, and notify operators.
Back to the old school and in order to determine whether your SQL Server system is performing optimally, take performance measurements at regular intervals over time, even when no problems occur, to establish a server performance baseline. Compare each new set of measurements with those taken earlier.
The following areas affect the performance of SQL Server:
- System resources (hardware)
- Network architecture
- The operating system
- Database applications
- Client applications
The minimum requirement of basline measurement is essential to determine:
- Peak and off-peak hours of operation.
- Production-query or batch-command response times.
- Database backup and restore completion times.
The format of Baseline and Benchmarking methods in SQL Server which helps the DBA to establish the performance baseline and helps to compare the baseline statistics to current server performance. Numbers far above or far below your baseline are candidates for further investigation, bigger or smaller is not the question and the important task is how good is your system and even smaller ones may indicate areas in need of tuning or reconfiguration. For example, if the amount of time to execute a set of queries increases, examine the queries to determine if they can be rewritten, or if column statistics or new indexes must be added.
So the first step to add on the tasks is identify the processes that are blocked for more than 10 seconds:
SELECT * FROM sys.sysprocesses WHERE blocked<>0 AND waittime > 10000
This brings a good old question by a SQL user that my application is designed that do not typically requests locks directly, then why do I see locking on my SQL Server? The answer is usual that locks are managed internally by a part of the Database Engine called the lock manager. When an instance of the Database Engine processes a TSQL statement, the Database Engine query processor determines which resources are to be accessed. The query processor determines what types of locks are required to protect each resource based on the type of access and the transaction isolation level setting. The query processor then requests the appropriate locks from the lock manager. The lock manager grants the locks if there are no conflicting locks held by other transactions, this is irrespective of how the application queries are managed and at best leave it to SQL Server than using your own hints.
So coming to the handy TSQL code that I use to query that will display lock information :
SELECT resource_type, resource_associated_entity_id,
request_status, request_mode,request_session_id,
resource_description
FROM sys.dm_tran_locks
WHERE resource_database_id = <dbid>
And this query which returns object information by using resource_associated_entity_id from the previous query. This query must be executed while you are connected to the database that contains the object.
SELECT object_name(object_id), *
FROM sys.partitions
WHERE hobt_id=<resource_associated_entity_id>
(you might observe that both the above code is available on BOL to get handy)
Also I would like to get the resource usage which returns information that associates a session ID with a Windows thread ID. This is essential to know how the Windows operating system manages the threads and how it helps on performance of the thread which can be monitored in the PERFMON tool and be aware that this query does not return session IDs that are currently sleeping.
SELECT STasks.session_id, SThreads.os_thread_id
FROM sys.dm_os_tasks AS STasks
INNER JOIN sys.dm_os_threads AS SThreads
ON STasks.worker_address = SThreads.worker_address
WHERE STasks.session_id IS NOT NULL
ORDER BY STasks.session_id;
GO
My next step of setup is to find how much memory has been allocation to the SQL Server from the operating system, though the general best practice is to leave it to DYNAMIC:
SELECT object_name as SQL_Server_Instance, cntr_value as Memory_in_KB FROM sys.dm_os_performance_counters
WHERE counter_name = 'Total Server Memory (KB)'
Finally it is best to see if there any deadlocks on the SQL Server using the good old DMVs and also obtain the older user session to see what is causing such a delay in performance:
SELECT * FROM sys.dm_os_performance_counters WHERE counter_name = 'Number of Deadlocks/sec' AND cntr_value <> 0
SELECT login_name as WHO_IS_HE, status as WHAT_IS_HE_DOING, program_name as WHAT_CLIENT_IS_HE_USING, login_time as WHEN_DID_HE_LOGIN, last_request_start_time WHEN_DID_HE_LAST_INTERACT FROM sys.dm_exec_sessions
WHERE is_user_process = 1 ORDER BY login_time ASC
--Web resource (credit to originator)
So handy process of using DMVs in SQL Server and PERFMON tool can provide information about SQL Server manages on individual resource types. It is not possible to prevent concurrent use of resources by different transactions. For example, if an exclusive (X) lock is held on a row within a table by a transaction, no other transaction can modify that row until the lock is released. Minimizing locks increases concurrency, which can improve performance. As usual the DMVs can be used by using two-part, three-part, or four-part names. They are my favourite as these Dynamic management functions on the other hand can be referenced in TSQL statements by using either two-part or three-part names.
There are many good tools available with SQL Server 2008 such as Management Data Warehouse and Extended Events to obtain the inforamtion quickly before you can find how to react when a problem strikes!
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.