Engine Watch

White paper Link: trouble shooting SQL Server 2005 performance problems

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

2006 greetings

This is my first blog posting for 2006. I went to china for a almost whole month at the end of 2005. I went to several cities. I presented at the SQL 2005 Launch Event in china. I had a great time.

Is lock grant in SQL Server First-In-First-Out (FIFO)?

Many people thought that lock grant in SQL Server was FIFO to avoid starvation problems.  Think about it. If someone is able to get in front of you in a super market checkout queue, you may never be able to get out of the store.

My friend Santtu who is the lock manager expert told me that SQL Server is actually smarter than that. FIFO has one disadvantage. It does not always allow the maximum concurrency. The lock manager in SQL Server 2005 allows as much concurrency as possible without starvation. Here is an example:

Transaction T1 has an IX lock on table Foo.  Transaction T2 runs a query on Foo and specifies the TABLOCK hint.  T2 becomes blocked behind T1 because its S lock is not compatible with T1’s IX.  Transaction T3 runs a select query on Foo without any hints – its IS request is granted immediately (i.e. before T2’s S request) because IS conflicts with neither IX nor S.  However if transaction T4 attempts to run an update statement on Foo, it will become blocked behind T2 because its IX request is not compatible with T2’s S requests and because T2 made its request first, it has priority. 

In SQL Server 2000, both T3 and T4 would have been blocked behind T2’s request.

SQL 2005 shipped (11/7/2005)

What an exciting five years. Areas I personally contributed: snapshot isolation and RCSI; page allocation and free space management; tempdb and a few other smaller things.

Link: SQL Server 2000 Build Chart

This could be pretty useful: a build chart lists all of the known KB articles, hotfixes and other builds of SQL Server 2000 that have been released:

http://www.aspfaq.com/sql2000Builds.asp

introducing the SQLCAT team blog

These guys works with the largest SQL Server databases:

http://blogs.msdn.com/sqlcat/

[SQL Server 2005] TEMPDB optimization

In SQL 2005 we have done quite a bit of internal improvements in tempdb scalability. These include:

• Caching of IAM and first data page for temp table and table variables.
• Improved allocation page latching protocol so that we use UP latch less frequently.
• Reduced logging overhead for tempdb so that we consume less IO bandwidth in tempdb log file.
• More efficient allocation algorithm for mixed pages in tempdb.

There should be less need of implementing the trace flag 1118. The TF is still there in case you need it.

We recommend the following if you see latch contention on tempdb allocation or system catalog pages:

Avoid auto grow. Pre-allocate space for tempdb files.

Make sure your temp tables are cached ( SQLServer:General Statistics Temp Tables Creation Rate)

Make as many tempdb files as you have CPU's (accounting for any affinity mask settings)

File sizes of equal amounts

[SQL 7.0] capacity planning tips

This is really old, but some tips are still potentially useful:

http://www.microsoft.com/technet/prodtechnol/sql/70/tips/storagen.mspx

SQL 2000: stored proc recompile

Old article but still very useful:

http://support.microsoft.com/default.aspx?scid=kb;en-us;243586

SQL 2000: what to do if tempdb grows to be very big

Unless your workload changed significantly, shrink file is not going to help you because tempdb will just grow back if a query needs that amount of free space. So a better way is to identify the query and try to optimize that, maybe by adding an index. The query that needs a lot of space in tempdb usually is a long running query. So you could start from the longest running queries. If you see a spool, or sort, or hash join in the query plan, that could be an indication of tempdb usage.

To prevent tempdb from consuming too much space on your disk, you could set up a max size for tempdb file. However, if you do this, make sure it is large enough. Otherwise your query may fail if it could not get the needed space.

On SQL 2005 there are better ways. I will post something later.

SQL 2005: TSQL enhancements

Click the following link to learn things like: TOP, RANK, ROW_NUMBER, PIVOT, Recursion...

http://www.awprofessional.com/articles/article.asp?p=327394&seqNum=7&rl=1

[sql 2005] Q: How do we verify page checksum in a database to make sure I have no hardware problem?

DBCC CHECKDB will do just what you want.  The PHYSICAL_ONLY option is probably the fastest way to get the checksums validated.

[SQL 2005] Q: How to find out how much space is used in database ?

Somebody asked me this question. So here it is:

SQL 2005 still supports the following three ways to report data and log space usage for a database:

    DBCC SQLPERF ( LOGSPACE )  can tell you the log size and used size.

    sp_spaceused  for space used/reserved in data files, not for log files.

    Perfmon.exe to view the database size and log size:

        SQL Server: Databases Object

            Data File(s) Size (KB)

            Log File(s) Size (KB)

            Log File(s) Used Size (KB)

In addition, SQL 2005 introduces the following two system views which can give you more information about space used by a table. These are the building blocks for space usage information that we expose via various tools.

    sys.allocation_units

    sys.dm_db_index_physical_stats

Please refer to BOL for their definition. Please note that there is some slight difference between sys.dm_db_index_physical_stats and sys.allocation_units.

The summary page in Management studio reports the space usage in data files. it should be the same as sp_spaceused.

SQL2005 CLR: two white papers

DBA focus:

http://www.sqlskills.com/resources/Whitepapers/SQL%20Server%20DBA%20Guide%20to%20SQLCLR.htm

Developer focus:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqlclrguidance.asp

SkyServer based on SQL Server

This is a quite old paper (Jan 02):

http://research.microsoft.com/~gray/Papers/MSR_TR_O2_01_20_queries.pdf

I was reading some news on astronomy which mentioned the SDSS. So I looked up this paper for some more detail. I thought you might like the way this paper approached physical database design and performance analysis, which is my interest.