Performance Tuning is a integral part of the DBA Activities and to say it is the Top Job Requirement too, which encapsulates the DBA’s major Skills. Performance Tuning is a very enormous topic and cannot be covered in one paper or topic, but can provide you a stepping stone in the right direction. I discuss here what i do and how i approach performance issues also slicing information for wannabe DBA’s. Every Database Professional has his own perspective of direction he wants to take and cannot be debated and can become a learning source.

There are several things you need knowledge of as a Database Server Performance Tuning Expert and i have tried to simplify the understanding as much as i can in this article.

Having Database Internals Knowledge will help you in long run, but having a good knowledge in SQL Server overall environment will server the purpose, as to perform your duties.

• Understanding client / business / server needs. (be a very good listener) : What i mean by a very good listener is to really pay attention and write the points down if you can, and not start forming solution as you dig into the issue/problem or as the user explains.
• Have good knowledge about (Blocking / Deadlock / Query Plans / Profiler / Event Viewer / SQL Error Logs / Good Understanding of Indexes (Fragmentation) / Protocols (Named Pipes/TCPIP/Shared Memory) / File Groups / Shrinking (Database and Transaction Log) / system databases.
• Profiler Traces : Know what to capture in profiler trace and how to analyze them.
• Creating and reading Performance Monitor counters.(Perfmon)
• Hands on using basic T-SQL for your DMV’s / system SP’s.
• SQL Server CPU / Memory Management Techniques (know your sp_configure) or SSMS.
• DISK IO Monitoring / Management in respect to Database Data Files.
• Understanding of TEMPDB Database Management. http://blogs.msdn.com/sqlserverstorageengine/archive/tags/TempDB/default.aspx
• Good understanding about RAID (Redundant Array of Independent Disks) Technologies and Advantages of Placing Data / T-Log /Tempdb files on various RAID formations.
• ~ Background about Windows OS (Processes and CPU / Memory Management)
• Difference between Several Edition of Windows and SQL Server and how a combination of those can help in improvement of resource usage. (e.g.: upgrading to SQL Server 2008 over 2005 will be advantageous as 2008 has built-in compression that allows you to compress the database files and the transaction log files / backup compression / Built-in SQL Server 2008 Policy Management  etc)
• Hands on experience with performance tuning can be advantageous.
• Going Further knowledge about how to improve SSIS / SSAS / SSRS Performance can be advantageous.
• Last but not the least good command on BING or Google Search: (you know for what?)

There might be more additions and subtractions to the above pointers, but this is a generalized specs of sql server performance monitoring requirements.

Always remember, For a Database Server, I/O is the primary reflector. Since Database servers do more I/O Intensive work than bandwidth related. we need to focus at index fragmentation / disk fragmentation / Disk IO / How the CPU / Memory is used? This should be the primary focus of a performance tuning expert.

Suggestions to users are definitely good, but should be limited and depend on existing resources/hardware in place, but wrong recommendations can crash up in the middle of nowhere as in “CAST AWAY” and you are on your own forever. So read a lot of whitepapers, before you actually start looking at suggestions/solutions.

When starting a performance tuning task, always remember these small pointers.

• Listen to what the performance issue is. A good listener and lots of question are good for you and make your life easier, or even can give you the solution right away, but wait and re-think twice.
• Check to see if the users/server has a existing baseline file to compare solutions/results with.
• Ask about how the issue appeared in the first place. what time it appeared. how is the system configured (CPU / Memory / Disk configuration).
• Analyze to check for, human caused error or hardware failure or system generated issues(Software/Hardware).
• Check Editions of SQL Server and Windows Used along with configuration parameters.
• Check the SQL Error Logs for that particular time (and compare with the similar times before days) / Get Event Viewer Logs and analyze them as much as you can.)
• How critical is this issue (Production will be affected or production work can still continue)
• Check the Process Monitor/Task Manager for a Quick View of the system resources. Or use SYSINTERNAL Suite from Microsoft to further analyze further (http://technet.microsoft.com/en-us/sysinternals/default.aspx)
• Check to see if any 3rd party Drivers are installed on the servers. or any other applications are installed which might be the cause of the conflict.
• Is this a clustered / replication / log shipping / mirroring  environment. Dig into the details, because these environments might need a diff approach and strategies.
• Check sp_configure and sys.sysprocesses OR sys.dm_exec_requests / sys.dm_exec_sessions / sys.dm_exec_connections for processes running and any blocking /deadlock in the system.
• Check for Index fragmentation (sys.dm_db_index_physical_stats) / overlapping indexes. – Too many HEAP tables in the queries can be Trouble too. Try to reduce the # of Heap tables. Also check to see if additional Logically correct indexes can be created on table, but remember additional index are even overheads for the system and can damped your table.
• Too Many varchar and big varchar columns might slow the Queries.(restrict the length of the varchar columns, if there is nothing you can do about the data type itself)
• Run Perfmon counters in windows, for the activity you want to Log (DISK / CPU / MEMORY).
• Run Profiler Trace for Databases level performance issues.
• Let PAL analyze your Perfmon Counter Log files some times. (http://www.codeplex.com/PAL)
• If these are several queries in question, check Query Plans. Check for Complex JOINS and related objects.
• Do not kill blocked / required Queries (because it is lucrative), without knowing or sharing the effects with the system owner.
• Document each and every step in the process. (document each change to implement on the system)
• Build a Roll back plan for each step.
• Use Bing and Google wisely (you know what?)

Quick Example of a Performance Tuning Scenario:

Working with Parallelism :

We had a situation where one of our SQL Servers 2005 STD SP3 Edition on Windows 2003 SP2 Standard Edition, used for Solar Winds Network performance monitoring Database had a great deal of performance issues.

After the users reported to me about the slowness and blocking, i started with some common sys.dm_exec_requests / exec_sessions / exec_connections queries and the good old sysprocesses. i saw bunch of blocking on the server. I used SQL Profiler to further filter down the results and captured some more queries (SP’s) that were posing an issue. I checked for index fragmentation with sys.dm_db_index_physical_stats DMV and found fragmentation for some critical table to be above 80%, I got down to several Stored procedures and created some NC indexes on the related tables(based on the where clause) to manage those blocking issues and it worked to a extent and also Tried to defragment the indexes using ALTER INDEX. This system had lot of long varchar fields (I am not furnishing the details.) But i could still see some blocking even after the initial index Maintenance. This server had 2 Socket 2 Cores each = 4 CPU (Hyper threaded) with 4 GB of RAM, and data and log file were all on the same volume E which was a Local RAID5, presented as C and E volumes. so nothing much can be done there.

Next thing i went for was from sys.dm_exec_requests i saw CXPACKET waittype to be occupying 40% of the total waittype’s. Following that lead, I checked the signal_waits for CPU Bottleneck with sys.dm_os_wait_stats and wanted to see the waiting queries in Queue on CPU. I was able to see that there was sufficient signal_waits to raise my doubts even more, then i checked sys.dm_os_wait_stats for wait_statistics and found CXPACKET number as the main culprit. CXPACKET relates to Parallel Queries or Parallelism and shows the Parallelism Option is Putting LOAD on CPU for Execution of Parallel Queries to speed up the process. Now i knew what i wanted to do with this info in hand.

I opened up the sp_configure with:

sp_configure ‘show advanced option’,’1′
reconfigure with override

Max degree of parallelism :  Signifies the number of CPU’s used to Run parallel Queries. Default is 0, which tell SQL Server to use all available processors. Setting this value to 1 will turn off Parallelism also the cost threshold for parallelism will be ignored.

Cost threshold for parallelism : This setting is in seconds and tells the Query Processor to start using parallel query execution after a certain time (elapsed time in seconds). This is ignored if the Max Degree of Parallelism is set to 1. Default value is 5.

I had to Turn off the Max degree of parallelism using the value as “1”. and checked the Server after a couple of minutes and everything was back to normal. the server was at good performance levels.

sp_configure ‘Max degree of parallelism’,’1’
reconfigure with override

This was one of the Performance issues i faced on work.

So what knowledge was used: Blocking / Deadlock / Simple T-SQL / DMV’s + system SP’s / Sp_configure / CPU / MEMORY / Query Parallelism / Profiler /Index Fragmentation knowledge.

The total workaround took me less than 2 hours. I Guess when you become familiar with your environment and performance tuning strategies, things become simple and can be acted upon much faster.