Tips
A closer look at CXPACKET wait type in SQL Server
If you have a server that has more than one CPU core, you may experience high values of CXPACKET wait types. This is typically due to queries that run in parallel and the real issue is to understand how different versions of a query can impact CXPACKET waits. In this tip we examine how changing a query can impact CXPACKET waits.
Always have a good plan! What's in your SQL Server Plan Cache?
The SQL Server Plan Cache plays a significant role in the overall performance of your SQL Server. Cached query plans allow for more efficient operations by saving time and resources. Therefore, if you experience a slow-down of your SQL Server Queries and Stored Procedures, it is extremely important to identify what exactly is in the plan cache, how is the plan cache is currently allocated, and are plans being re-used.
Analyzing SQL Server Plan Cache Performance Using DMVs
So far I see you have covered persisting SQL Server performance counter values obtained from sys.dm_os_performance_counters and querying the results for buffer pool information. What about the other aspect of SQL Server memory: plan cache? In this tip we look at some queries to pull this data.
Analyzing the SQL Server Plan Cache
The cache management mechanism plays an important role in performance of any system. Like any reliable DBMS, SQL Server enjoys a sophisticated cache management system for optimized performance without the need for any user intervention. There are ways to add a plan or data to the SQL Server cache or to remove a plan or data from SQL Server cache, but these techniques are only recommended for testing or troubleshooting purposes. Keeping in mind the importance of the cache mechanism, how could one get the plans and their usage statistics in SQL Server?
Automate Performance Monitor Statistics Collection for SQL Server and Windows
You have about 100 SQL Servers installed in your production environment. You have performance problems on few of the servers, but they happen during the time when you are not watching the servers. So, how can you automate performance statistics collection on all the servers around the clock so we have the statistics for 24/7/365.
Avoid SQL Server functions in the WHERE clause for Performance
SQL Server offers many handy functions that can be used either in your SELECT clause or in your WHERE clause. For the most part these functions provide complex coding that would be very difficult to get this same functionality without these functions. In addition to the built in functions you also have the ability to develop your own user defined functions. When functions are used in the SELECT clause to return uppercase output, a substring or whatever, it doesn't affect performance that much, but when functions are used improperly in the WHERE clause these functions can cause major performance issues.
Built in Performance Reports in SQL Server 2005
Finding a good reporting mechanism for your SQL Server environment can be tedious and time-consuming-you can either write your own reporting application or choose a third-party solution. You may also have to install an instance of Reporting Services in your environment, depending on the needs of the application. SQL Server 2005 includes a number of built-in reports to assist you in troubleshooting and measuring performance.
Collecting and Storing Poor Performing SQL Server Queries for Analysis
In an ideal world all of our queries would be optimized before they ever make it to a production SQL Server environment, but this is not always the case. Smaller data sets, different hardware, schema differences, etc. all effect the way our queries perform. This tip will look at a method of automatically collecting and storing poor performing SQL statements so they can be analyzed at a later date.
Collecting performance counters and using SQL Server to analyze the data
Quite frequently I find myself in situation where I need to get detailed information on performance monitor counters. For example I need to determine which processes are consuming all CPU at certain times. I find it handy to push the performance monitor counters into SQL Server where I can query it or perhaps display it in Reporting Services. This tip will cover the steps to do this.
Convert Implicit and the related performance issues with SQL Server
I was a running a routine query using an equal operator on the only column of the primary key for a table and I noticed that the performance was terrible. These queries should have been flying because all I was doing was retrieving one row of data which should have been doing an index seek. When I looked at the query plan it was doing a scan instead. This tip shows you what I found and how to resolve the problem.
Correlating Performance Monitor and SQL Server Profiler Data
Just about all SQL Server performance related tips talk about using data from Performance Monitor and data from SQL Profiler. The big draw back has always been that they are two totally separate tools, with different interfaces and therefore you need to figure out a way to correlate the data between the two applications on your own. This was the old way of doing things, but with SQL Server Profiler for SQL 2005 this has changed.
Create Custom Data Collections for SQL Server Data Management Warehouse
Out of the box, SQL 2008 comes with 3 pre-canned data collection scripts. However, it is possible to create custom made data collection scripts that are fully integrated with SQL Server Data Management Warehouse. In this tip we walk through the steps of how do this.
Creating SQL Server performance based reports using Excel
In a previous tip, "Setting up Performance Monitor to always collect performance statistics" I wrote about how to collect performance monitor data, but once you have the data then what do you do with it. In this tip I will show you how I use Excel to analyze the data to help determine where your bottlenecks may be and also an easy way to create quick reports and charts for your SQL Servers.
Do SQL Server User Defined Datatypes (UDT) affect performance?
Using User Defined Datatypes (UDTs) in the database has two major advantage; first, for columns that should have same data type and are, for example, compared or joined, by using same UDT, you can make sure that they are really compatible and secondly, you could use UDTs to fetch a list of all columns of the same specific type, i.e.: an ID number, by viewing the UDT's dependencies. There is a legend among SQL Server developers and DBAs saying that UDTs may degrade performance. In this article I prove whether this believe is right or wrong.
Error running the SQL Server Database Tuning Advisor for non SA users
A user with db_owner access was trying to run the Database Tuning Advisor (DTA) on a newly built SQL Server, unfortunately he was getting this error message: "Failed to open a new connection. In order to start tuning application needs to create some objects in MSDB database. You have to be a member of sysadmin fixed server role in order to initialize MSDB for tuning (DTAClient)." Do you have any suggestions on how to solve this issue? Check out this tip to learn more.
Finding SQL Server Deadlocks Using Trace Flag 1222
I have been getting sporadic deadlocks on my SQL Server. How can I track down the queries that are causing the deadlocks so I can resolve the issue? In this tip we show how this can be done with trace flag 1222.
Gather IO statistics down to the SQL Server database file level
When managing your SQL Server environment there are many aspects that need to be reviewed to determine where the bottlenecks are occurring to ensure you are getting the best performance possible. SQL Server offers many great tools and functions to determine issues with locking, blocking, fragmentation, missing indexes, deadlocks, etc... In addition, to looking at all of these areas another area of concern is I/O. Disk I/O can be tracked at the OS level by using counters in Performance Monitor, but these counters give you an overall picture of what is occurring on the server. What other options are available to look at I/O related information down to the file level for each database?
Getting IO and time statistics for SQL Server queries
If you're like any other DBA or developer in the world, writing sound T-SQL code and performance tuning is one of your primary duties. There are many tools, both native to SQL Server and third-party, that assist you in this endeavor. But in thinking about writing and tuning queries, what should the focus be? For many, the length of time taken to execute the query is "good enough". One focus should be on the resources used by the server, since the length of time taken to execute the query can vary based on other server activity. In addition to using Profiler and Execution Plans, consider using SET STATISTICS IO and SET STATISTICS TIME.
How To Collect Performance Data With TYPEPERF.EXE
As a DBA I like to take advantage of command line tools when I am working on performance tuning and optimizing my SQL Server databases. One of the things I typically need to do is to collect performance data on the server which includes CPU, memory and disk utilization as well as SQL Server-specific data. What command line tools are available to do this?
How to create a SQL Server Clone Statistics Only Database
Is there a way to reproduce and diagnose query compilation and plan issues without actually having the data or the entire database? In this tip I will walk you through how to create a Statistics Only database.
How to find out how much CPU a SQL Server process is really using
Someone has reported a performance issue for your application. When you look into the database server you see CPU utilization is very high and the SQL Server process is consuming most of the CPU. You launch SSMS and run sp_who2 and notice that there are a few SPIDs taking a long time to complete and these queries may be causing the high CPU pressure. At the server level you can only see the overall SQL Server process, but within SQL Server you can see each individual query that is running. Is there a way to tell how much CPU each SQL Server process is consuming? In this article I explain how this can be done.
How to Identify IO Bottlenecks in MS SQL Server
We experience regular slowdowns on our SQL Server databases. After analyzing the memory and CPU usage we would like to continue the root cause investigation by examining I/O bottlenecks. In this tip we look at ways to recognize I/O related bottlenecks in SQL Server.
How to Identify Microsoft SQL Server Memory Bottlenecks
We experience regular slowdowns on our MS SQL Server database. We would like to start the root cause investigation by examining memory bottlenecks. What is your recommendation to uncover memory bottlenecks in SQL Server?
How to Identify SQL Server CPU Bottlenecks
We experience regular slowdowns on our MS SQL database. After analyzing the memory usage we would like to continue the root cause investigation by examining CPU bottlenecks. In this second tip we look at how to identify CPU issues related to SQL Server.
Identify SQL Servers with inefficient power plans using Policy Based Management
A lot of people are coming to the realization that their CPU performance may be hindered due to a poor default (and recommended) setting in Windows Server 2008 and Windows Server 2008 R2: the "Balanced" power plan. As Glenn Berry reports in a recent blog post, with no other changes, simply switching from the balanced power plan to the high performance power plan can yield an improvement of roughly 20%. If you are currently using the "Power Saver" plan, you can expect an even more dramatic improvement.
Identifying Key and RID Lookup Issues and How to Resolve
Formerly known simply as "bookmark lookups", Key or RID lookups can represent performance issues that don't always bubble to the top of your tuning efforts, especially when your data is still relatively small. Non-covered queries can be a problem because, for every row in the index, the additional column(s) must then be fetched; this can have a significant impact on large data sets and impact overall performance. In this tip, we look at how to identify and resolve the issue.
Improving SQL Server performance when using table variables
Batches or store procedures that execute join operations on table variables may experience performance problems if the table variable contains a large number of rows. In this tip we look at an option to make this faster.
Interesting example of statistics and index usage on a SQL Server computed column
Why index and statistics are not always used on a computed column. In this tip we look at how SQL Server generates a query plan based on a computed column and different methods to have it use the index.
Introduction to SQLDIAG for SQL Server Performance Monitoring and Tuning
In order to monitor and tune SQL Server you need to collect and review all sorts of information such as performance counters, SQL Server Profiler traces, the output from many dynamic management views (DMVs), the Windows Event Log and SQL Server Error Logs. You can collect performance counters using the System Monitor, run the SQL Server Profiler to capture events from SQL Server, query DMVs using SQL Server Management Studio, run the Database Tuning Advisor to analyze things, etc. It's overwhelming just talking about it. Is there a single tool that I can use to collect all the information I need?
Issues with running DBCC SHRINKFILE on your SQL Server data files
Why would database performance remain low and fragmentation high event though the entire database has been defragmented every night? In this tip we cover how SHRINKFILE works and some of the issues it might create.
Monitor, Diagnose, Administer and Automate SQL Server Tuning
Download this free trial software SpotlightŪ on SQL Server Enterprise.
More intuitive tool for reading SQL Server execution plans
Anyone responsible for performance tuning or troubleshooting should be familiar with the graphical execution plan feature of SQL Server Management Studio. This allows you to visually display the execution plan for a particular query (or multiple plans in a batch). There are weaknesses in this feature, however. For example, the display is quite monochrome, where all operator node icons essentially look alike - making it difficult to quickly spot the most expensive node, or the most expensive subtree. It is also limited to showing overall costs by combining CPU and I/O, affecting your ability to isolate high-cost bottlenecks limited to one metric or the other. Data movement between nodes is represented in such a way that a thicker arrow means
NORECOMPUTE option of UPDATE STATISTICS in SQL Server
Updating statistics is valuable for ensuring the SQL Server optimizer has the current statistical information to most efficiently process the query results. As a best practice, the UPDATE STATISTICS command should be issued on a regular basis to provide SQL Server with the most recent data. Automatically updating statistics is possible with the 'Auto Update Statistics' database configuration, but this could cause performance issues if a large table's statistics are updated during the middle of the day. To prevent this problem, the 'Auto Update Statistics' option can be disabled on a per database basis. This paradigm creates the need to rebuild all of the statistics in a manual manner. Does a happy medium exist to let SQL Ser
Optimize Parameter Driven Queries with SQL Server OPTIMIZE FOR Hint
SQL Server does not always select the best execution plan for your queries and thankfully there are several different hints that can be used to force SQL Server into using one execution plan over another. One issue that you may be faced with is when using parameters in your WHERE clause, sometimes the query runs great and other times it runs really slow. I recently had a situation where the hard coded values in the WHERE clause worked great, but when I changed the values to parameters and used the exact same values for the parameters the execution plan drastically changed and the overall time it took to run the query increased by about 5 times. This situation is referred to as parameter sniffing where SQL Server stores the values used as
Perfmon Counters to Identify SQL Server Disk Bottlenecks
There are several articles about identifying I/O issues related to SQL Server. There are various methods for finding I/O bottlenecks, so which performance counters are needed for a quick evaluation of a possible disk bottleneck?
Performance Advantages of SQL Server Filtered Statistics
When running queries SQL Server may not always use the correct statistics to determine the best optimization for a query plan. This could be a performance hit for complex queries and have correct statistics is key to the optimizer. In this tip we look at how filtered statistics can improve query optimization.
Remove some SQL Server blocking issues with the NOLOCK hint
Often many applications utilize the same database and it may be quite difficult to manage locking issues that occur which may lead to significant blocking issues and therefore potential performance issues. In a perfect world this would never be the case and everything would run without issue. Unfortunately locking and blocking are real things that occur regardless of how well you architect your database application, so the name of the game is to minimize this as much as possible. As mentioned already, there may be multiple applications hitting the same database such as a transaction based application and a reporting based application. So what can be done to minimize the blocking issues?
Setup Performance Monitor to always collect SQL Server performance statistics
Very often when performance problems arise there are a few tools that I always first look at using to help troubleshoot SQL Server issues. One of these tools is Performance Monitor. This tool is usually run interactively and only for a short duration which does not give you a full picture of what is going on with your server. In addition, it is often the case that when you needed to collect the information the time has already passed and therefore the opportunity is gone. How can we setup Performance Monitor to collect data all of the time, so we can go back and review the data when needed?
Speed up SQL Server queries with PREFETCH
The SAN data volume has a throughput capacity of 400MB/sec; however my query is still running slow and it is waiting on I/O (PAGEIOLATCH_SH). Windows Performance Monitor shows data volume speed of 4MB/sec. Where is the problem and how can I find the problem?
SQL Server 2008 64bit Query Optimization Trick
Have you ever come across a situation where a query will perform well most of the time, but as soon as you change the predicate value, the same query is 10 to 30 times slower? In this tip I will cover a trick to fake out SQL Server.
SQL Server Database Engine Tuning Advisor for Performance Tuning
I have identified one production database in particular where the business users are constantly complaining about slow performance. I would like to focus strictly on identifying and mitigating issues related to query performance in that database. What's the best tool to analyze a workload and identify improvements? In this tip we look at how to use the Database Engine Tuning Advisor (DTA) to identify performance issues and resolutions.
SQL Server Database Specific Performance Counters
Both SQL Server and Windows offer a lot of data to help troubleshoot and monitor overall usage and performance for your SQL Server databases. Within SQL Server there are several DBCC commands as well as a lot newly exposed data from the Dynamic Management Views in SQL Server 2005. One way of monitoring your individual database usage may be to use the data from sysprocesses. This data is also exposed by either using the GUI tools or by running sp_who2. This may be helpful, but this may give you too much information as well as still having the need to extract the data to figure out what is going on within each database. So what other tools exists to capture database level data?
SQL Server Graphical Query Plans Tutorial
In this tutorial we will cover how to create SQL Server graphical query plans and how to read them to assist in tuning your queries.
SQL Server Performance Monitoring Tools
Being able to identify SQL Server performance issues at the drop of a hat is easier said than done. Without a means to collect and analyze the performance data it is difficult at best to understand and correct the items in a timely manner. SQL Server ships with a handful of tools to include Profiler, Sysmon\Perfmon and the Database Engine Tuning Advisor\Index Tuning Wizard. Much of the time these tools meet the needs for manual collection and analysis, but what if you need to go beyond the tools that are available and to resolve a performance issue quickly?
SQL Server Performance Tuning and Monitoring Tutorial
SQL Server is a great platform to get your database application up and running fast. The graphical interface of SQL Server Management Studio allows you to create tables, insert data, develop stored procedures, etc... in no time at all. Things run great initially, but over time performance degrades. This is where performance monitoring and tuning come into play.
SQL Server performance tuning for each layer of an application
Performance is always a key factor when using SQL Server and should be thought about before the initial setup of the server as well as when the application is being used in production. In this tip I cover the approach I like to take when setting up and tuning my SQL Server environments.
SQL Server Schema Binding and Indexed Views
Recently while creating an archival job I noticed that job was taking way too long to complete. To help troubleshoot I executed the stored procedure directly and it was also taking a long time to fetch the rows from the view that I was using. Based on the query plan it looked like creating an index on the view may help the issue, so I first looked to see if any indexes were in place for the views, but none were found. The next step was to create an index on the view, but I was presented with this error message "Cannot create index on view, because the view is not schema bound".
SQL Server statements currently running with fn_get_sql
If you have ever had the need to troubleshoot current processing that is occurring on your SQL Server there are a few different ways to see what is going on such as: Enterprise Manager, sp_who2 or querying the system tables. Another option is to use the "::fn_get_sql" statement.
System Monitor (Perfmon) Counters for SQL Server 2005
Capturing performance monitor counters is of great value to understand how SQL Server is behaving. Without this data it is difficult to determine where the performance issues are occurring. Capturing the metrics has been traditionally from Performance Monitor, but in SQL Server 2005 you can now get this information from one of the new dynamic management objects.
The High Performance SQL Server DBA
Download this free SQL Server whitepaper and learn how to be a high performance SQL Server DBA.
Tool to help you analyze SQL Server SQLDIAG and PSSDIAG output
SQLDIAG or PSSDIAG are excellent tools used to collect and analyze data in order to identify possible performance root causes. The issue is that the output produced by such utilities is sometimes convoluted, hard to read and hard to understand. In this tip, I will show you a tool that you can use to read and analyze the data collected by these tools.
Top 10 Tips for Optimizing SQL Server Performance
Download this free SQL Server whitepaper and learn how to optimize SQL Server performance.
Trending Buffer Pool Performance Using DMV sys.dm_os_performance_counters
In this tip we look at how to use the persisted data that was captured from the DMV sys.dm_os_performance_counters. Specifically we look at how to use this data to analyze performance data related to the Buffer Pool.
Trick to Optimize TOP clause in SQL Server
Have you ever come across a situation where a SELECT query with a TOP clause will perform well most of the time, but as soon as you change the TOP value the same query is 10 to 20 times slower? In this tip I will show you why this may happen and ways to prevent this.
Troubleshooting Performance Problems in SQL Server 2005
With the introduction of SQL Server 2005 came many changes. One of these dramatic changes was how to find and troubleshoot performance issues. SQL Server 2005 introduced dynamic management views which gives you insight into various aspects of SQL Server. A previous tip was written about DMVs to introduce the topic as well as highlight some of the DMVs. Even thought Microsoft has exposed a lot more performance related information in the DMVs, interrupting the results or even knowing what to do is still a problem. Although there is not a hard and fast rule as to what to do, I did come across a great whitepaper that does shed some light as to what can be done and also how to interpret some of the results.
Using schema binding to improve SQL Server UDF performance
SCHEMA BINDING is commonly used with SQL Server objects like views and User Defined Functions (UDF). The main benefit of SCHEMA BINDING is to avoid any accidental drop or change of an object that is referenced by other objects. A User Defined Function (UDF) may or may not access any underlying database objects, but in this tip we show how using SCHEMA BINDING with a UDF can improve performance even if there are no underlying objects.
Waiting on Waits
Download this free SQL Server whitepaper and learn how to optimize SQL Server by identifying what SQL Serer is waiting on using Dynamic Management Views.
Windows Reliability and Performance Monitor to troubleshoot SQL Server
SQL Server DBAs are expected to know some basic Windows Server administration skills. Traditionally, one of the Windows tools DBAs would learn to use is the Performance Monitor. DBAs who knew how to use perfmon in Windows Server 2003 may still find the Reliability and Performance Monitor in Windows Server 2008 a bit of an unknown when they first see it. However, in addition to all the existing features from the previous versions, this applet now offers some new functionality that can make performance troubleshooting a lot easier. In this tip, I will try to give an overview of this tool.
