cache store flush

I came across this post while going through links for the cache store flush messages -
http://blogs.msdn.com/sqlprogrammability/archive/2007/01/17/10-0-plan-cache-flush.aspx

a "cool" system

http://www.sqlskills.com/BLOGS/PAUL/post/High-end-backup-compression-numbers.aspx
isn't that cool?

dmo with sql2k8

I personally have been a great fan of Sql DMO for many years while managing hundreds of sql server boxes with a small team. It helped me a lot in audit and common maintenance tasks. With the introduction of Smo, Dmo became a deprecated feature in Sql2k5. With Sql2k8, MS has gone one step further and avoided installing Dmo automatically. However, if you still want to use Dmo for Sql2k8, you can download the feature pack from here -
http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en. However, it would be nice to start using Smo and Powershell now.

refreshing intellisense cache

The intellisense shipped with sql2k8 SSMS is something very helpful. There is a way to refresh the local Intellisense cache to reflect the latest changes. Go to Edit --> IntelliSense --> Refresh Local Cache.

recompilations by temp tables Vs table variables

There is an interesting post from the sqlcat team on the difference the recompilations make while using a temp table or table variable. See it here - http://blogs.msdn.com/sqlcat/archive/2008/10/09/table-variable-vs-temp-table-and-estimated-vs-actual-query-plan.aspx
There are a few other links that may be useful -
http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx
http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html
http://support.microsoft.com/default.aspx/kb/305977

cu1 for Sql2k8

Hardly a month since the release of sql2k8, the first cu has been released. Find the link here - http://support.microsoft.com/kb/956717. To see the instructions on the installation, go here http://blogs.msdn.com/psssql/archive/2008/09/26/welcome-to-sql-server-2008-patching.aspx

changing default backup directory

Ever wondered how to change the default path for backups while using SSMS? Open registry editor and open HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSqlServer, where x is the instance number. On the right pane, modify the key value for BackupDirectory to the path of your choice.

auditing table access in Sql2k8

It is difficult in auditing the SELECT staments in SQL Server. As triggers cant be used for this purpose, trace seems to be the only option until v2005. The new audit objects in v2008 should be a cool new feature to improve the audit tasks. http://blogs.msdn.com/euanga/archive/2008/05/28/sql-2008-how-do-i-tell-if-a-specific-table-is-still-being-used-in-an-app.aspx

rebuilding SQL Server 2008 system databases

Here is a post from the pss team on how to rebuild the system databases in SQL Server 2008. Find the post here http://blogs.msdn.com/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

sql2k5 scale out articles

Find a bunch of links to find the ins and outs of scaling out with Sql Server 2005 -
http://blogs.msdn.com/sqlcat/archive/2008/06/12/sql-server-scale-out.aspx

multi-server query with SSMS 2008

A cool new feature with SSMS 2008 http://blogs.msdn.com/sqlrem/archive/2008/02/04/multiple-server-query-execution-in-sql-server-2008.aspx

cumulative update 9 released

SQL Server 2005 Sp2 cumulative update 9 has been released. Happy installations!
http://support.microsoft.com/kb/953752

zeroing for log files

Instant file initialization is an excellent feature that came with Windows 2003 which is made use by SQL Server 2005. When instant file initialization is enabled, the zeroing process for the SQL Server data files is disabled which reduces substantially the time required for file write and growth processes. However, instant file initialization cannot be done for SQL Server log files. The reason is that there are parity bits required by log file sectors to help in crash recovery processes, and if zeroing is disabled, the already existing data in the sectors can be misinterpreted as parity bits and can cause issues. There is a post from Paul Randal explaining the architectural reason. Find the post at http://www.sqlskills.com/blogs/paul/2008/08/20/SearchEngineQA24WhyCantTheTransactionLogUseInstantInitialization.aspx.

SQL Server 2008 Released!

After the long waits, Microsoft has officially announced the release of the new version of SQL Server to our lives! Read the full story here http://www.microsoft.com/Presspass/press/2008/aug08/08-06SQLServer2008PR.mspx
Along with that, a new edition - "Web Edition" - also has been announced which would be used for internet facing web applications http://blogs.msdn.com/irelandlicensing/archive/2008/07/11/microsoft-sql-server-2008-web-edition.aspx#8824528

Database Versions

As DBAs, these days we get databases of different types. Say, databases that have been created with the current version or databases that belong to old applications that have gone through a few upgradations (from 7 to 2000 to 2005 etc). The question is how can we find out what was the original version of SQL Server when the database was created. There is an undocumented command to find this out - DBCC DBINFO. You can see two entries in the output - dbi_version and dbi_createversion. The value for dbi_version denotes the current version of the database while dbi_createversion denotes the version of SQL Server when the database was originally created. A value of 515 stands for SQL Server 7.0, A value of 539 stands for SQL Server 2000, a value of 611 stands for SQL Server 2005 and a value of 612 stands for SQL Server 2005 with vardecimal option enabled. For more information on DBCC DBINFO command, see Paul's post at http://www.sqlskills.com/blogs/paul/2008/07/11/SearchEngineQA20BootPagesAndBootPageCorruption

Tools to avoid SQL Injection

Owing to the recent mass SQL injection attacks, Microsoft has worked to come up with a few tools to reduce the chances of attacks of the same type. Find the link here -
http://blogs.technet.com/swi/archive/2008/06/24/new-tools-to-block-and-eradicate-sql-injection.aspx

Tracking FileGrowth

I was always of the opinion that we should avoid using the “autogrow” option ON for production databases - whether small applications or large applications), simply because of the lot of overhead it creates in the expansion of the file, connection issues while the expansion happens, and most importantly, the amount of fragmentation it creates. Recently while going through a production issue, I heard this question from a fellow administrator that how we can find out when a file growth happened (This is a database where the autogrow is set to ON). I dug through some resources, but accidentally found out that is getting tracked in the default trace. We can just0 query the trace file to obtain the growth events. Joining the result with sys.Trace_Events will give us more clear results. Run the below given query and ensure that the path to the trace file is correct -
SELECT TrcEv.Name, Trc.DatabaseName, Trc.FileName, Trc.StartTime FROM fn_trace_gettable(E:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_6.trc', NULL) AS Trc INNER JOIN sys.trace_events TrcEv ON Trc.EventClass = TrcEv.trace_event_id WHERE TrcEv.Name LIKE '%Auto Grow'
ORDER BY StartTime.
If you want to see, how the initial size of the file and the growth factor affects the file growth (and the index fragmentation of course), try the small example given below-

CREATE DATABASE GrowthTest
GO
USE GrowthTest
GO
CREATE TABLE dbo.Employee
(EmployeeName VARCHAR(3000))
GO

DECLARE @i INT
SET @i =1
WHILE @i < 10000
BEGIN
INSERT INTO Employee VALUES(REPLICATE('A', 3000))
END
(I assume that the filegrowth for your model database is the default settings) After this, run the above given query once more to see how many times the autogrowth happened.

Sql2k8 on August pricelist

Microsoft officially announcing that SQL Server 2008 will be released in August, and that too with no price increase!
http://www.eweek.com/c/a/Application-Development/Microsoft-to-Deliver-SQL-Server-2008-in-August/
http://blogs.technet.com/dataplatforminsider/archive/2008/07/09/sql-server-2008-available-on-august-price-list.aspx

Backups with COPY_ONLY option

We have heard a lot that having regular backups is a must in any database environment. But, there are cases where having even regular full, differential and log backups will not ensure that the database is secure, and you can lose the work for hours or days. This happens due to backup chain break when someone takes a full backup apart from the regular scheduled backups and the subsequent differential backups will have a different backup_lsn. The remedy is using the COPY_ONLY option with full backups which doesn’t break the chain. Paul Randal has illustrated the importance of this with a post which I noticed recently only http://www.sqlskills.com/blogs/paul/2007/10/15/BACKUPWITHCOPYONLYHowToAvoidBreakingTheBackupChain.aspx. So as a DBA, next time when your developers ask you for a backupset of a production database, either restore it from a regular backupset or take a backup with the COPY_ONLY option.

Reorganization of Non-Clustered Indexes

We all have seen people getting confused on the indexes reorganization processes. One of doubts that often popup is that whether the clustered index reorganization of a table will automatically reorganize all the non-clustered indexes on the table. Well, the answer is that it depends on the version and on the type of the clustered index column.In case of SQL server 2005, the answer is straight forward – the non-clustered indexes will NOT be reorganized at all. If the version is SQL Server 2000, it depends on the clustered index. Let us discuss SQL Server 2000 first. In SQL Server 2000, if the table is a heap, then the non-clustered indexes will locate the row corresponding to a key, based on the physical recordid which consists of the pageid and the record slot. This is a physical value. So in a heap, the lookup reference is a physical location. In case we create a clustered index on the heap or remove the clustered index on a table, these physical locations may get changed due to the rearrangements. Hence, the lookup references for the non-clustered indexes will become invalid which means the non-clustered indexes need to be reorganized.
Now, let us consider that the table is not a heap and has a clustered index. In case the table has a clustered index, the lookup is NOT based on the physical recordid. This is because if there is a clustered index, the lookup can uniquely identify the record using the clustered index key. This is logical reference, because the reference is towards a clustered index key and not to any physical location. However, if we drop the clustered index from the table, the logical lookup references based on the clustered index will become invalid, and hence the non-clustered indexes have to be reorganized.
Now, let’s see what will happen when we reorganize an existing clustered index. We know that a clustered index can be unique or non-unique. To ensure the uniqueness of non-unique clustered index keys, internally SQL Server automatically adds a uniquifier to every key of the clustered index (This does not happen with a unique clustered key). Now, if the non-unique clustered index is reorganized, the uniquifier is recreated, which will in turn make the lookup references invalid. So, the non-clustered indexes have to be recreated to ensure the integrity. But, if the clustered index is a unique clustered index, there is no uniquifier and therefore no uniquifier recreation is required during reorganization. Hence, the non-clustered indexes need not be recreated.
All the above things are the same in SQL Server 2005 except that the uniquifiers are not recreated for a non-unique clustered index during a index reorganization. Instead of recreating the uniquifiers, it will reuse the old uniquifiers. This means, even if the non-unique clustered index is recreated, there is no chance of the lookup references becoming invalid. So, there is no need of recreating the non-clustered indexes neither for a unique clustered index nor for a non-unique clustered index. Hence we can summarize as follows –
(Click on the image for a larger view)
Now, you can go back and see whether your index maintenance plans and scripts require any modification!