Today's post I will focus on SQL Server performance tuning using the easiest and targeted tool in my arsenal.
This approach is one of many, and I will post additional articles as time permits.
My goal is to move this blog to LinkedIn, and begin posting my Enterprise Software Tuning life's lessons.
Scenario
If you have an application that when running performs slowly, and you suspect it may be related to database, please read on! This article can be useful from the novice to the expert software developer or systems operator.A typical scenario I see is performance of an application is acceptable but degrades due to increased concurrent users (or service calls) or increased database size over time.
The performance problem maybe due to code, server resources, or database tuning. Over the last 15 years of performance tuning i can say with confidence that 80% of all performance issues is related to the database. The database server resources or administrator is usually not directly at fault. Typically the application makes database requests that are not tuned to respond quickly.
As volume increases and concurrent requests are made, the requests that respond slow will respond slower, request that are highly frequent will create an overload, and request that are intense on disk IO will degrade all aspects of software operation.
In short, the data requests for one user that take too long, high on CPU use, high on memory use, high with disk requests, or generate higher network bandwidth will exponentially degrade performance with volume.
As an example, if a data request takes 5 seconds to respond, when 10 uers hit the same request about the same time, you will now have 10 concurrent processing events each taking 100% of a CPU thread for full 5 seconds. This will drown out quicker quests making them slower.
Think of your computer as a 4 lane highway, if 4 slow trucks pace each other, all the quick requests degrade quickly. It is important that when performance degrades to identify the slow trucks, minimizing them to keep lanes open for faster requests.
Identifying the resource blockers in SQL Server
First, you must gain direct access to your SQL Server database. It is essential that if you are not familiar with database access to treat this access with utmost respect. If your data is ever corrupted, data maybe lost forever and restoring operations very expensive.This example in this post will focus on using commands that are safe to use in any environment. But once access is given, you have the power to analyze, create, or destroy data.
Access to SQL Server
Choose a method of access, then jump down to heading Credentials.Direct Access
To gain access to SQL server there are three basic options.First, if you have access to the host database server, log onto the server and find SQL Server Management Studio. Simply click start > Programs > Microsoft SQL Server 20## > SQL Server Management Studio
Remote Access
You can install SQL Server Management Studio on your laptop, providing your network access to the server is permitted. You may find downloads of SQL Server express from Microsoft or full version from your administrator.http://technet.microsoft.com/en-us/sqlserver/bb265254
Once installed, access as described in Direct Access.
Third Party Remote Access
There are tools not related to Microsoft SQL Server that you can use to gain direct access to databases. The open source tool Squirrel allows remote access to any database with a Java JDBC driver.You can install Squirrel (or other tool) then download and install SQL Server JDBC driver and configure.
Please refer to the tool's online documentation on how to do this.
Credentials and Query Window
You must have user name, password, server name information to connect remotely to SQL Server. If on direct access usually the log-on information is saved and simply click OK/ The user you log in on MUST have server administrator access to get the information we need.
.If you have access to the enterprise application, this information can be found in the enterprise configuration for database use. The user may or may not have administrator access.
Once connected navigate to left side of SQL Server Management studio and find the target database. Right click on database name and choose new query. A window on right side of the screen will appear like notepad. In this area you can enter database requests and click the execute button.
Identifying the problem data requests
Copy the text below and paste into the Query window on right side of the Microsoft SQL Server Studio program.DECLARE @OpenQueries TABLE (cpu_time INT, logical_reads INT, session_id INT) INSERT INTO @OpenQueries(cpu_time, logical_reads, session_id) select r.cpu_time ,r.logical_reads, r.session_id from sys.dm_exec_sessions as s inner join sys.dm_exec_requests as r on s.session_id =r.session_id and s.last_request_start_time=r.start_time where is_user_process = 1 and s.session_id <> @@SPID waitfor delay '00:00:01' select substring(h.text, (r.statement_start_offset/2)+1 , ((case r.statement_end_offset when -1 then datalength(h.text) else r.statement_end_offset end - r.statement_start_offset)/2) + 1) as text , r.cpu_time-t.cpu_time as CPUDiff , r.logical_reads-t.logical_reads as ReadDiff , r.wait_type , r.wait_time , r.last_wait_type , r.wait_resource , r.command , r.database_id , r.blocking_session_id , r.granted_query_memory , r.session_id , r.reads , r.writes, r.row_count, s.[host_name] , s.program_name, s.login_name from sys.dm_exec_sessions as s inner join sys.dm_exec_requests as r on s.session_id =r.session_id and s.last_request_start_time=r.start_time left join @OpenQueries as t on t.session_id=s.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h where is_user_process = 1 and s.session_id <> @@SPID
In upper left hand corner of the grid presented right click and select copy with headers.
Paste this information into EXCEL for easier viewing and sending to others.
Do this multiple times during performance problems, take at least 10 samples over time to get a glimpse of the issues.
Any SQL that appears here is SQL that took more than 1 second to resolve for a wide variety of reasons. In my professional work, any SQL that takes over 1 second needs to be examined. 98% of the SQL i deal with can be tuned down to 1/1000th of a second to put this into perspective.
1 second may not seem alot, but most programs call 100's of times to render information, if 10 of the SQL statements take 1 second, the end user is waiting 10 seconds and the server resources are being heavily used for 10 seconds by one user, affecting everyone else.
Columns to focus on
There are many columns in this SQL result set, however there are a few key columns to focus on.blocking_session_id - the data request is being blocked by another request. These types of issues can dramatically degrade performance. the more requests, the more blockers. Think of the 4 lane highway going down to one lane for 10 feet.
Wait time - Duration SQL has been running in microseconds.
What to do?
Once you know where the problem is, need to address it. Contact your software vendor to correct the issues. Typically slow SQL can be corrected simply by adding indexes.For how to profile SQL to tune, see post Analyze a Query
Other SQL
You can find many other useful SQL on this shared google drive I keep with SQL I find.https://docs.google.com/spreadsheets/d/1zqo-XrwqELRWg-xedl5oDfCRkEB6p0PWnILKDklnwZg/edit?usp=sharing
