Top 10 SQL Server Posts for 2011

2011 is nearly over. It’s time to list the Top 10 viewed SQLServer-DBA.com posts for the year , based on Views.

 

1. System Reserved Partition and monitoring
2. Data Loading Performance Guide
3. Mobile Commerce and SQL performance Tuning
4. Index Disabled and Index Rebuild
5. How to report a SQL Server performance problem
6. Attach database without log file and rename database
7. Define IOPs for all database Servers
8. RAID server configuration and disk amounts
9. Calculate disk IO throughput and MB per second
10. Window Perfmon scripting, SQL Server perfmon and how to perfmon

Related Posts

Top 10 DB2 posts for 2011

Author: Jack Vamvas (http://www.sqlserver-dba.com)

TCP Chimney offload

On Windows 2003 Enterprise Edition Service Pack 2 TCP Chimney was enabled by default. It took me a while to discover this feature. But once I did find it , I turned it off.

It was causing numerous problems on many applications , particularly modelling based , long running SQL Server transactions.  I also experienced problems with Microsoft Exchange.

Windows 2008 still has the TCP chimney offload functionality but the command syntax has changed.  On the article there is a command that shows the status & it seems that the chimney is disabled by default anyway.

 http://support.microsoft.com/kb/951037

The script to disable tcp chimney is :

 

netsh int tcp set global chimney=DISABLED

SET SHOWPLAN ON

Use SET SHOWPLAN_XML  instead of  SET SHOWPLAN_TEXT or SET SHOWPLAN_ALL. Microsoft announced at the  SQL Server 2008  release ,  SET SHOWPLAN_TEXT and  SET SHOWPLAN_ALL are deprecated

 The advantage of using SET SHOWPLAN_XML  ON  is  that the code is not executed.  Executing the t-sql returns the execution plan.

SET SHOWPLAN_XML ON
GO
select sql_handle from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 
GO
SET SHOWPLAN_XML OFF

Turn the option off by   SET SHOWPLAN_XML  OFF .

 If using the Results to Grid , the output is :

 

Clicking on the link , displays the Execution Plan.

 

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Optimize query for LIKE

I received a question from a client yesterday : "We've implemented a query with LIKE in the WHERE clause. But if I want to search for a suffix (LIKE '%suffix') an Index Scan is used and takes very long. Is there any way that SQL Server does an Index Seek in case of LIKE '%suffix'? How can I optimize? "

By it's nature a - '%suffix' will scan rather than seek. But , something like 'a%suffix' will seek. Note the 'a' before the '%'. The optimizer can make a guess on the basis of 'a'. If the '%' is in front , the optimizer cannot predict the outcome. As an example, inspect this index tree:

+----AAA----+
| |
+-ABB-+ +-CCC-+
| | | |
ADD AEE CFF CGG

An index is a binary tree. The query engine determines which branch of the tree to take by deciding if the value it's looking for is greater than or less than the value it's currently sitting on:

Seeking a value 'ABB': Two jumps gets us to the desired value, because we can navigate the tree.

Seeking a value of '%D', we start with the first node, 'AAA'. Which way do we branch to continue our search? We don't know, therefore we have to look at EVERY node to find those that end in 'G', therefore an index (or table) scan.

Another  approach is to store another column with the REVERSE() of the existing column, then querying with the REVERSE() of the search string, 'xiffus%'. This could also be achieved with an indexed view, saving the space in the base table. This would require some manipulation of the query string.

Author: Jack Vamvas (http://www.sqlserver-dba.com)

What is SQL Server Query Tuning?

I suggested to a  client last week ,Query Tuning is required to troubleshoot some SQL performance issues. He wanted a definition of Query Tuning. I sent him an email , defining a Query Tuning approach .These are notes from the email
 

What is Query tuning?

In practise, there are predeployment queries and post deployment queries. Both require optimisation. The first type need to be optimised, the second type need to be optimised ONCE they are identified as causing the problem.

DBA Strategy: The 80/20 rule

The 80/20 rule ,is it worth trying to spend 80% of your time to solve 20% of waits. A methodology is required to lead to systematically lead us to the main bottlenecks.
Ultimately the end user doesn't care about performance measures such as CPU counts, they are interested in getting a result. Typically , a wait for longer than 3 seconds is considered a performance issue.

Diagnosing a SQL Server Problem Method

A set based methodology . Repeatable and provable. Document everything as you go along

1.Analyse Waits at instance level
2.Match waits with queues
3.Conclusion & plan
4.Drill down to the db level
5.Drill down to the process level
6.Tune indexes/queries

Other considerations

1.Analyse waits at the instance level.
SQL 2005 use sys.dm_os_wait_stats ; SQL 2000 use DBCC SQLPERF(WAITSTATS) Focus on I/O, although there are other issues
2.Match waits with queries
use System monitor - e.g if most waits were I/O based use the different I/O queues
3.Determine course of action
What is the cause of the problem?
SQL Server Optimiser
SQL Server Instance/Database Configuration & Optimization
Design your db for performance

Set up an appropriate maintenance plan for data

Perfmon,Waits and Queues  is a good approach to analysing SQL Performance problems and SQL Query Tuning. Use Waits for a quick and broad approach to the main delays - correlate with Perfmon - and then decide on a course of action.

Poor indexes and queries are not the only cause of  performance problems. For example ,SQL Server – SOS_SCHEDULER_YIELD and how to reduce it  . Using the approach above - will assist in diagnosing other causes

Author: Jack Vamvas (http://www.sqlserver-dba.com)