Twitter

Follow SQLQuill on Twitter

Subscribe

MN Pass

General SQL 2005 Set Tips

1. SQL SERVER SETUP

Setup a SQL 2005 Standalone Machine

I would strongly recommend that you start by reading about the pre-requisites of installing SQL 2005.

http://msdn2.microsoft.com/en-us/library/ms143719.aspx

If you want to install SQL 2005 side-by-side with SQL 2000 or 7.0, you need to be aware of some special considerations. Refer to this article.

http://msdn2.microsoft.com/en-us/library/ms143694.aspx

After ensuring the pre-requisites are met, you should follow the steps mentioned in this article to install a SQL 2005 standalone instance on your machine.

http://msdn2.microsoft.com/en-us/library/ms143219.aspx

If you prefer to install SQL 2005 from command-prompt, you can do that too by following the steps highlighted in this article.

http://msdn2.microsoft.com/en-us/library/ms144259.aspx

Upgrade to a SQL 2005 Standalone instance from SQL 2000 and 7.0

The first step is to check if the upgrade from you existing SQL 2000 edition is supported. You can review the Version and Edition Upgrade Paths Matrix to learn more about it.http://msdn2.microsoft.com/en-us/library/ms143393(SQL.90).aspx

After you have confirmed that a direct upgrade path exists for your edition of SQL Server, you should do a sanity check by running the upgrade advisor. Some old features in SQL 2005 have either been deprecated or removed. To find out if you are using any feature that has been deprecated or removed in SQL 2005, run Upgrade Advisor tool against your SQL 2000 databases. This tool will give you a list of things to correct pre, during and post upgrade process in your database. I would strongly encourage you to run this tool in advance of your upgrade planning.

http://msdn2.microsoft.com/en-us/library/ms144256(SQL.90).aspx

After having confirmed that there are no upgrades blocking issues for your database, you should read more about some of the known issues with upgrade of each component of SQL Server and how to avoid them by visiting this link:

http://msdn2.microsoft.com/en-us/library/ms143710(SQL.90).aspx

Once the ground work is complete, you can upgrade your existing standalone instance in-place by following the steps highlighted in this article.

http://msdn2.microsoft.com/en-us/library/ms144267.aspx

Make sure that you follow the steps in the above upgrade article in the section titled Next Steps. This section talks about updating the statistics after upgrade and also about using DBCC UPDATEUSAGE. These steps are important from performance point of view.

Post Setup and Upgrade Activities

Once the setup is complete, it is important to verify that the SQL Server is configured and setup right. You can verify that by using the SQL Server setup logs. However, these logs are verbose and you need to know what you are looking for. To learn how to sniff the right information from these logs, refer to this aticle.

http://msdn2.microsoft.com/en-us/library/ms143702.aspx

Setup a SQL 2005 Cluster from ground-up

Since the minimum hardware and software requirements for SQL 2005 have changed, I would suggest that you start by reviewing this article. You can confirm that your hardware and software meets the minimum requirements for sure.

http://msdn2.microsoft.com/en-us/library/ms143719.aspx

There are special considerations for SQL 2005 Clusters that you should be aware of. Review this link to ensure that you have configured all the pre-requisites before running the SQL 2005 setup.

http://msdn2.microsoft.com/en-us/library/ms189910.aspx

Also, SQL 2005 cluster setup prompts you for domain accounts. You need to have these domain accounts setup in advance. Refer to this article which talks about what to do and some of the best practices for setting up domain accounts.

http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B915846

Once the pre-requisites are met, follow the instructions in this article to setup a SQL 2005 failover cluster.

http://msdn2.microsoft.com/en-us/library/ms179530(SQL.90).aspx

Upgrade from SQL 2000 and 7.0

The first step is to check if the upgrade from you existing SQL 2000 edition is supported. You can review the Version and Edition Upgrade Paths Matrix to learn more about it.http://msdn2.microsoft.com/en-us/library/ms143393(SQL.90).aspx

After you have confirmed that a direct upgrade path exists for your edition of SQL Server, you should do a sanity check by running the upgrade advisor. Some old features in SQL 2005 have either been deprecated or removed. To find out if you are using any feature that has been deprecated or removed in SQL 2005, run Upgrade Advisor tool against your SQL 2000 databases. This tool will give you a list of things to correct pre, during and post upgrade process in your database. I would strongly encourage you to run this tool in advance of your upgrade planning.

http://msdn2.microsoft.com/en-us/library/ms144256(SQL.90).aspx

After having confirmed that there are no upgrade blocking issues for your database, you can upgrade your existing cluster in-place by following the steps highlighted in this article.

http://msdn2.microsoft.com/en-us/library/ms191295(SQL.90).aspx

Once the setup is complete, it is important to verify that the SQL Server is configured and setup right. You can verify that by using the SQL Server setup logs. However, these logs are verbose and you need to know what you are looking for. To learn how to sniff the right information from these logs, refer to this aticle.

http://msdn2.microsoft.com/en-us/library/ms143702.aspx

Maintenance of a SQL 2005 Cluster

Here is a list of a few How-To articles that should come in handy to learn about the best practices of doing the mundane tasks.

http://msdn2.microsoft.com/en-us/library/ms190990(SQL.90).aspx

Also, on a cluster, a lot of stuff is different from a standalone box. Here is a list of clustered SQL do’s and don’ts – a must read.

http://support.microsoft.com/?id=254321

2. SQL SERVER GENERAL PERFORMANCE

SQL 2005 Whitepapers

Statistics used by SQL 2005

As we know, existence and the subsequent updates of statistics is a very important factor governing SQL Server query performance. However, the implementation details are different in SQL 2005. Understand the nuances by referring to this article.

http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx

Caching in SQL Server 2005 and how it impacts performance

SQL Server, just like any other RDBMS, tries to minimize disk I/O. It achieves this balance by caching the data in memory. However, since memory is a limited resource, it is imperative that you implement the best practices to maximize your use of it. This is an excellent white paper that talks about the internals of SQL Server caching, with a focus on compilations and re-compilations of batches in SQL 2005.

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

Performance Analysis and Troubleshooting SQL Server 2005

This is an excellent whitepaper that provides scripts and queries to monitor, identify performance bottlenecks and suggests improvements. I would strongly recommend this – a must read.

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

SQL 2005 Webcasts

I thought I will also provide some links to useful webcasts. I love the format of the webcasts and the ease of replaying stuff at will.

Performance Diagnosis in SQL 2005

All production databases need to be high performing. However, often times, we don’t have solid performance benchmark numbers which we should aim and maintain in the long run. The first step, then is to monitor SQL Server performance during the good and bad times. Based on the data collected during bad times and comparing them to the one collected during good times, we can see what is causing this slow performance. This 300 level webcast talks about performance monitoring and troubleshooting best practices.

http://msevents.microsoft.com/cui/webcasteventdetails.aspx?eventid=1032275587&eventcategory=5&culture=en-us&countrycode=us

Troubleshooting Performance Problems in Microsoft SQL Server 2005

Once you have monitored the performance over a period of time, you can use the techniques in this webcast to troubleshoot the performance bottlenecks. This webcast provides some really good practical advise on troubleshooting performance.

http://msevents.microsoft.com/cui/webcasteventdetails.aspx?eventid=1032275647&eventcategory=5&culture=en-us&countrycode=us

SQL 2005 Blogs

The folks who wrote the code in SQL Server have blogs of their own. These blogs provide in-depth knowledge about SQL Server internals – like the query optimizer, the SQL OS etc. – stuff that is not there either in Books Online or at MSDN. I would strongly encourage you to read these blogs, if you really want to learn about the esoteric internals of SQL Server.

Slava Oks Blog on SQL OS and Memory

http://blogs.msdn.com/slavao/

Blog on Query Optimizer written by the folks who wrote the query optimizer

http://blogs.msdn.com/queryoptteam/

SQL Tips and Tricks Blog – handy tips and tricks to do mundane tasks in SQL 2005

http://blogs.msdn.com/sqltips/default.aspx

3. SQL SERVER BLOCKING

Here is the list of resources which you can use to learn more about SQL Server blocking concepts and how to troubleshoot them.

So what is blocking?

Blocking is an unavoidable characteristic of any relational database management system (RDBMS) with lock-based concurrency. On SQL Server, blocking occurs when one SPID holds a lock on a specific resource and a second SPID attempts to acquire a conflicting lock type on the same resource.

Identifying blocking

Microsoft has published a script that you can use to monitor blocking on your server. This script creates a SP, which continuously pings syspro
cesses to obtain information about different processes and their waittypes. This is a very handy script and you can collect data over a period of time to analyze the behavior.

http://support.microsoft.com/kb/271509/EN-US/

Understanding the nuances of blocking

This KB talks at length about the different scenarios in which you can experience blocking. You will learn how to identify blocking in SQL 2000, which tools to use and how to resolve blocking using some of the best practices.

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

Another scenario in which you can experience blocking is lock escalation in SQL 2000 and 2005. You can identify blocking caused by lock escalation by following the steps highlighted in this KB.

http://support.microsoft.com/?id=323630

Based on the output of the blocker script in KB 271509, you can see the different waittypes for each process. To help you decipher the meaning of each waittype, refer to this KB.

http://support.microsoft.com/?id=822101

Special Considerations for SQL 2005

In SQL 2005, it is recommended that you use the DMVs instead of the regular system tables. In SQL 2000, we used to query sysprocesses, but it is only provided for backward compatibility in SQL 2005 (http://msdn2.microsoft.com/en-us/library/ms179881.aspx). Instead, you can use sys.dm_os_wait_stats (http://msdn2.microsoft.com/en-us/library/ms179984.aspx)

You can still use the blocker script in KB 271509 against SQL 2005. However, a better way to identify and resolve blocking in SQL 2005 is described in this whitepaper under the section titled Blocking.

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#EYBAG

In the appendix, you will find a simple query which you can run recursively to get the output of the DMVs. You can store this output in a table or a flatfile and use the same strategies to resolve blocking in SQL 2005.

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>