Engine Watch

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

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.

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...

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.

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

These guys works with the largest SQL Server databases: http://blogs.msdn.com/sqlcat/

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...

This is really old, but some tips are still potentially useful: http://www.microsoft.com/technet/prodtechnol/sql/70/tips/storagen.mspx

Old article but still very useful: http://support.microsoft.com/default.aspx?scid=kb;en-us;243586

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...

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

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

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...

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

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...

http://sqldev.net/articles/WaitTypes.htm

The following KB: http://support.microsoft.com/kb/328551 talks about how many files the tempdb database should have. Is there similar suggestion for regular databases? Here is my thought: It is hard to predict how many files are needed per file group...

A useful article: http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/default.aspx And this webcast: http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032263279&EventCategory=5&culture=en-us&CountryCode...

This is a very good white paper on this important topic http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

Temp table is no different from regular tables. We may choose either page or extent allocation to optimize performance and space utilization. The kinds of operation affect how we choose: select into bulk insert create index insert...

I just found this article which mentions tempdb optimization, and other things useful: http://www.scalabilityexperts.com/default.asp?action=article&ID=195

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

I am too busy lately. So here is just a link to something you can download and listen to: http://www.sqldownunder.com/

Found two useful things on the internet today: Planning for Consolidation guide from microsoft: http://www.microsoft.com/technet/prodtechnol/sql/2000/plan/sql2kcon.mspx A guideline from network appliance: http://www.netapp.com/tech_library...

<Rewrote from my newgroup post> Case: 4GB database with two "big" tables: 1.5 million records, table with almost 1 million records, and each month, import about 300,000 records, also delete some old records. database goes up to 5 or 6 GB and...