Data Services

cache store flush

2008-12-05T14:07:00.000-08:00

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

2008-11-12T08:05:00.000-08:00

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

dmo with sql2k8

2008-10-14T07:33:00.000-07:00

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

2008-10-14T07:08:00.000-07:00

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

2008-10-14T05:08:00.000-07:00

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

2008-10-06T06:07:00.000-07:00

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

2008-09-22T02:55:00.000-07:00

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

2008-09-18T23:14:00.000-07:00

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

2008-09-01T02:49:00.000-07:00

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

2008-08-21T22:11:00.000-07:00

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

2008-08-21T21:52:00.000-07:00

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

2008-08-21T04:56:00.000-07:00

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

zeroing for log files

2008-08-21T04:21:00.000-07:00

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!

2008-08-07T04:45:00.000-07:00

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

2008-07-28T06:37:00.000-07:00

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

2008-07-21T04:09:00.000-07:00

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

2008-07-17T05:45:00.000-07:00

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

2008-07-10T23:29:00.000-07:00

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

2008-07-10T23:19:00.000-07:00

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

2008-06-12T05:33:00.000-07:00

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!

SQL Server 2008 RC0

2008-06-12T03:30:00.000-07:00

SQL Server 2008 RC0 is now available for download; find it at http://download.microsoft.com/download/2/0/c/20cf6e18-0448-4e7b-8d8c-60a3c4802671/Download_Instructions_ENU.htm

Fragmentation with Shrink

2008-05-26T02:38:00.000-07:00

Whenever I hear questions on database shrinking, always my first question would be “why you need to shrink”. It is not easy for me to understand why people always look for shrinking a database whenever they see some free space within the database. I always say, forget that the space there; it can be of use later. I would recommend database shrinking only in the following conditions –
1. The database is getting archived
2. We are damn sure that the database won’t grow anymore.
3. The database has a huge amount of free space and we need to restore the database to a server that doesn’t have enough disk space.
A database shrink has too many disadvantages –
1. Increased database fragmentation.
2. Increased file fragmentation.
3. The resources utilization to do the shrink and then the required growth in future
4. The user downtime during the growth and shrink process.
There is a small but great post from Paul Randal on the autoshrink and autogrowth processes, and how it increases fragmentation. Paul being one of the best SQL Server professionals in the world and a long time program manager in the Storage Engine team, I dont think there are many who can say something as convincingly as Paul on this topic. A must read post for any production DBA. See his post here http://www.sqlskills.com/blogs/paul/2007/11/13/AutoshrinkTurnItOFF.aspx

Database Change Logging

2008-05-19T03:55:00.001-07:00

Recently, I was asked to configure process with which the developers will get to know the changes that happened on the dev boxes. This was important because we are having new application pilot run in parallel with the existing system, and there were different departments and a lot of teams involved in the development and QA tasks. I thought of sharing how we accomplished this.
The process we agreed upon is to use DDL triggers to track the schema changes, and send the data on a weekly basis to the concerned people.
The first thing we did was to create a log table in the databases. After creating the table, we created a ddl trigger on the database, so that this trigger will get fired on every schema change on the database. The trigger captures the eventdata for each schema change and inserts the information such as login name, host name, complete xml event data etc into the Log table. This helped us to have a complete log on the changes happening on the database schema. Following is the code for the table creation and ddl trigger.
**********************************************************************************
-- Script: CretaTable_DatabaseLog.sql
-- Author: RAJAN JOHN
-- Date: 05/08/2008
--**********************************************************************************
USE [MyDatabase]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DatabaseLog](
[PostTime] [datetime] NULL,
[DatabaseUser] [varchar](100) NULL,
[hostname] [varchar](100) NULL,
[Event] [varchar](100) NULL,
[Schema] [varchar](100) NULL,
[Object] [varchar](100) NULL,
[XmlEvent] [xml] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

**********************************************************************************-- Script: SendDatabaseChangeLog.sql
-- Author: RAJAN JOHN
-- Date: 05/08/2008
--**********************************************************************************
**********************************************************************************
USE [MyDatabase]
GO
/****** Object: DdlTrigger [ddlDatabaseTrigger_DatabaseChangeLo]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [ddlDatabaseTrigger_DatabaseChangeLog]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
SET NOCOUNT ON;

DECLARE @data XML;
DECLARE @schema sysname;
DECLARE @object sysname;
DECLARE @eventType sysname;
DECLARE @LoginName sysname;
DECLARE @SPID sysname;
DECLARE @hostname varchar(100);

SET @data = EVENTDATA();
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname') ;
SET @LoginName =@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname') ;
SET @SPID =@data.value('(/EVENT_INSTANCE/SPID)[1]', 'sysname');
SELECT @hostname=hostname FROM master.dbo.sysprocesses WHERE spid=@SPID
INSERT [dbo].[DatabaseLog]
([PostTime],
[DatabaseUser],
[Event],
[Schema],
[Object],
[XmlEvent],
[hostname] )
VALUES
( GETDATE(),
@LoginName,
@eventType,
CONVERT(sysname, @schema),
CONVERT(sysname, @object),
@data,
@hostname );
SET NOCOUNT OFF
END;
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [ddlDatabaseTrigger_DatabaseChangeLo] ON DATABASE

Now, the next step was to provide a report on the schema changes to the concerned people (the development leads, QA leads etc). Initially, we thought of running a query on the log table on a weekly basis, and copy-paste the resultset and send via email. Then we decided on automating this process as well. We wrote a job that uses database mail and send the results of the query to the concerned people. Following is the code for the sp that is run by the job.

*************************************************************************************
-- Script: SendDatabaseChangeLog.sql
-- Author: RAJAN JOHN
-- Date: 05/08/2008
--*************************************************************************************
*************************************************************************************
Declare @DeBug bit
Declare @strServerName varchar(255)
Declare @mailRecipients varchar(max),
@mailImporance varchar(6),
@subjectString nvarchar(255),
@mailBody nvarchar(max),
@mailQuery nvarchar(max),
@resultAttachFlag bit,
@resultAttachFileName nvarchar(255),
@resultColSeperator char(1),
@resultWidth int
--*************************************************************************************
set @DeBug = 1
select @strServerName = CASE CAST(isnull(ServerProperty('InstanceName'), '') AS VARCHAR(100))
WHEN '' THEN CAST(ServerProperty('MachineName') AS VARCHAR(100))
ELSE CAST(ServerProperty('MachineName') AS VARCHAR(100)) + '\' + CAST(isnull(ServerProperty('InstanceName'), '') AS VARCHAR(100))
END
set @mailImporance = 'High'
set @subjectString = N'Weekly Database Change Log - ' + @strServerName + '\MyDatabase'
set @mailBody = N'Please find below the database changes from ' + cast(dateadd(wk, -1, getdate()) as varchar)+ N' and ' + cast(getdate() as varchar)
set @mailQuery = N'SELECT [PostTime], [DatabaseUser], [HostName], [Event], [Schema], [Object], [XmlEvent]
FROM HrTaxMasterDB.dbo.DatabaseLog WHERE DATEDIFF(wk, [PostTime] , GETDATE()) <= 1' set @resultAttachFlag = 1 set @resultAttachFileName = N'Database Change Log' set @resultColSeperator = char(9) set @resultWidth = 32767 --************************************************************************************* if @DeBug = 1 begin set @mailRecipients = N'Rajan.JOhn@MyCompany.com print @SubjectString + char(13) + @mailBody execute msdb.dbo.sp_send_dbmail @profile_name = 'Database Change Log Profile', @recipients = @mailRecipients, @subject = @SubjectString, @body = @MailBody, @body_format = 'TEXT', @importance = @mailImporance, @query = @mailQuery, @attach_query_result_as_file = @resultAttachFlag, @query_attachment_filename = @resultAttachFileName, @query_result_separator = @resultColSeperator, @query_result_width = @resultWidth, @query_no_truncate = 0
end

Capacity Planning - Sizing Memory

2008-05-19T01:13:00.000-07:00

Physical memory is one of the important hardware components for a database server. This is because of the fact that, adequate physical memory decreases the physical IO for the server by reusing as much as the data and query plans that exist on the cache.

SQL Server Memory Requirements
SQL Server does dynamic memory allocation. This means SQL Server will allocate whatever memory left on the server that is not being used for its functioning. Whenever any application or the operating system requires additional memory, SQL Server releases memory for their use.
Different components of SQL Server require physical memory for their functioning. The following sections describe the components within SQL Server which are the main users of physical memory.
Buffer Cache Requirements
SQL Server buffer cache is the area in the physical memory where the data pages and index pages are cached. The buffer cache is the largest consumer of physical memory in SQL Server. The dynamic allocation and de-allocation of memory by SQL Server is done mostly by adjusting the size of the buffer pool. The server should be provided with adequate memory to ensure a very high cache hit ratio and the amount of physical IO be reduced to a very minimum level. A decrease in the buffer cache size increases physical IO activities.
Procedure Cache Requirements
Procedure Cache is the area in physical memory where SQL Server caches the compiled execution plans for queries and stored procedures. This is the second largest consumer of physical memory in SQL Server. A healthy size of procedure cache is required to reduce the number of query compilations and optimizations. A decrease in the size of procedure cache increases recompilations which in turn increases processor overhead.
Connection Context Requirements
Each user connection to SQL Server requires a certain amount of memory for handling the user session. This comes around an average of 500KB per connection. Hence, larger the set of concurrent users accessing the database, larger will be the amount of memory required to support the connections.
Other Requirements
There are other components in SQL Server that require memory. These include the lock manager etc.
The details on SQL Server memory allocation can be obtained by the command “DBCC MEMORYSTATUS”
See the hardware comparison details for different SQL Server editions.

Sizing Memory through Load Testing
The best method to estimate the physical memory requirements for a database application infrastructure is through load testing.
We need to do the simulation for the application load with the maximum number of concurrent users and transactions possible, and have to take the memory consumption. Physical memory has to be allocated enough to avoid any memory bottleneck.
A decrease in memory affects the whole system. Memory deficiency increases cache misses and paging, paging increases physical IO and these all increase the processor utilization.

Sizing Memory through Workload Analysis
In all cases, its will not be possible to create the infrastructure prototype and do simulations. In such cases, memory sizing can be carried out by manually doing a workload analysis and calculating the memory requirements by the different components of the server infrastructure.
We can have an example of a sales database application that experiences an annual user accounts growth of 15% and sales data growth of 20%. The following table shows a rough estimation of the memory requirements for procedure cache, user connections etc, and how that will affect the buffer cache.

This is just a rough estimation, assuming that one user connection requires around 500KB and the SQL Server, OS program components together require around 1GB. Similarly, we can do accurate calculations and project the memory requirements.

With this, I am concluding the current Capacity Planning series. I will be demonstrating on a few tools, trends etc in some future posts.

Capacity Planning - Planning the Processor Subsystem

2008-05-06T03:00:00.000-07:00

Planning the processor subsystem for a database infrastructure is a straight forward task. Normally, all the high end servers will be using the latest available best processors. If all the individual servers are already loaded with the best the maximum supported number of best available processors, the way to increase the processing power is to increase the number of servers. These two ways of increasing processing power is explained in the sections below.
Scale-up vs. Scale-out
Scale up is the process of increasing the processing power of an already existing server either by adding more processors or by increasing the processing power of the processors. Scale out is the process of increasing the processing power of a database infrastructure by increasing the number of servers so that the load can be distributed among the different servers. Both these approaches have got their benefits and downsides.
Scale up is normally considered as a quick fix to resolve processor bottlenecks for database applications. With the arrival of hot swappable CPUs and databases supporting them, this approach doesn’t require little downtime and lesser overhead of planning. The major advantage is that, since it does not increase the number of servers, there is no extra overhead in terms of maintenance, monitoring, licensing etc.
Though scale up can be done easily as a quick fix for issues, it has its downsides. The major disadvantage is that there is always a maximum gain we can get by scaling up. This is because there are limitations for the maximum number of processors supported for a particular operating system as well as the RDBMS. Hence, scaling up cannot be considered as a permanent solution especially when the application is an ongoing growing application.
Scale out is normally considered as a permanent solution for increasing processing power. The most important advantage of this approach is the expandability. We can add more servers and make the application to use the newly added servers.
Since scale out is a more permanent solution, it requires a lot of planning and overhead. The most important disadvantage is that the application should be redesigned to make use of the multiple servers. This may not be an easy task for ongoing applications, and will be a costly affair. There are other disadvantages as well. These include the increased overhead of extra servers for maintenance, monitoring licensing etc.
We can decide whether to go for scale up or scale out depending upon the current situation of the application, and what is the future of the application.
Factors for Considerations
While selecting the processors, there are a few factors to be considered and see how they affect the application environment.
Ø Processor Architecture
Nowadays, processors are available in 32bit and 64bit architectures. We can decide which architecture our infrastructure requires based upon the cost and scalability requirements of the application.
The other important factors that have to be considered when selecting the processors are the following. Since, they are known to most people, I am not explaining each of them.
o Processor Cores
o L2 Cache
o Hyper threading