A few weeks ago, I wrote a post related to the tempdb where I attempted to answer the question – Is TempDB a copy of the model database? The post attracted a couple of follow-up comments (which I really appreciate, because it gives everyone a great deal to think about) and led me to a thought-filled week-end.

If you have not read my previous post, I strongly recommend that you read it by going to the link here – SQL Server – TempDB – Is it a copy of the Model database?

SCHEMA v/s Storage – Influence of the model database

Most administrators often make changes to their SQL Server database configuration. These changes typically include (and are not limited to):

• Changing the default sizes and growth values of the data and log files
• Changing the number of files & file-groups associated to a database
• Changing the location of their database files
• Changing the database collation
• Implement mirroring, log shipping, etc
• As demonstrated in my previous post, objects can be created in the model database, so that they are available whenever a database is created
  • Because the tempdb is re-created every time the SQL Server restarts, this is applicable to the tempdb also

Most of these changes can be classified into the following major categories:

• Storage Parameters (size & number of files, collation, etc)
• Security (changing roles and permissions)
• Availability & Disaster Recovery (mirroring, log shipping, etc)
• Schema changes (Defining or manipulating objects)

Each of these changes need to be applied either during database creation or after. These changes therefore need to be persisted somewhere so that they can be picked up during database creation/recovery.

However, the basic schema of any new database within Microsoft SQL Server will always come from the model database, and as demonstrated in my original post this is also applicable to the tempdb. Hence, while any database is schematically a copy of the model database, the storage & other parameters may differ based on the values specified by the user.

Is the initial size of a database independent of the model database?

This question is a tricky one to answer. So, let’s do a simple, 2-part experiment. First, let’s bring our environment up to the state mentioned in my previous post – SQL Server – TempDB – Is it a copy of the Model database?. This can be done by running the following script and restarting the SQL Server instance. Notice that as per the referenced post, tempdb gets copied over from the model database.

/**********************************************
              !!!WARNING!!!
THIS SCRIPT IS PROVIDED AS-IS AND WITHOUT
WARRANTY. THE AUTHOR AND nakulvachhrajani.com (@SQLTwins)
ARE NOT RESPONSIBLE FOR ANY DAMAGE CAUSED 
BY USING THIS SCRIPT.
**********************************************/
USE model
GO
CREATE TABLE DefaultTableTest
( DataId INT IDENTITY(1,1),
  RandomId INT DEFAULT (RAND()*1000),
  DataName VARCHAR(10) DEFAULT 'DEFAULT'
)
GO

/**********************************************
   Insert some test data into the table
**********************************************/
USE model
GO
INSERT INTO DefaultTableTest DEFAULT VALUES
GO 600000

Part 01 – Influence of the model database’s size on the tempdb

Now, remove the test object from both the model and the tempdb databases. Also, let’s use the ALTER DATABASE…MODIFY FILE clause to change the default size of the tempdb.

/**********************************************
              !!!WARNING!!!
THIS SCRIPT IS PROVIDED AS-IS AND WITHOUT
WARRANTY. THE AUTHOR AND nakulvachhrajani.com (@SQLTwins)
ARE NOT RESPONSIBLE FOR ANY DAMAGE CAUSED 
BY USING THIS SCRIPT.
**********************************************/
--Remove the object from the model database
USE model
GO
DROP TABLE DefaultTableTest
GO

--Remove the object from the tempdb database
USE tempdb
GO
DROP TABLE DefaultTableTest
GO

--Alter the tempdb to use an initial size of 10MB as opposed to the current size (approx. 21MB)
ALTER DATABASE tempdb
    MODIFY FILE ( NAME = 'tempdev',
                  SIZE = 10MB
                )
GO

Let’s restart the SQL Server instance, and verify the size of the tempdb.

As you can see, the tempdb continues to be of the same size as the model database. So what went wrong? Well, what’s wrong is that ALTER DATABASE…MODIFY FILE cannot modify the size such that it is smaller than the current size. Per Books On Line (http://msdn.microsoft.com/en-us/library/bb522469.aspx): “If SIZE is specified, the new size must be larger than the current file size.”

Now, let’s see if CREATE DATABASE can create a database for us that has an initial size which is less than that of the model database.

Part 02 – Influence of the model database’s size on a newly created database

We will pickup from where Part 01 left off, i.e. the objects in the model database are gone, and both the model and the tempdb are about 21MB each.

Let’s try to create a database by specifying an initial file size which is less than that of the model database:

/**********************************************
              !!!WARNING!!!
THIS SCRIPT IS PROVIDED AS-IS AND WITHOUT
WARRANTY. THE AUTHOR AND nakulvachhrajani.com (@SQLTwins)
ARE NOT RESPONSIBLE FOR ANY DAMAGE CAUSED 
BY USING THIS SCRIPT.
**********************************************/
USE [master]
GO

CREATE DATABASE [InitialFileSizeDefined]
ON PRIMARY (NAME = 'InitialFileSizeDefined_Data', 
            FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.DENALICTP03MSSQLDATAInitialFileSizeDefined_Data.mdf',
            SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1MB)
LOG ON (NAME = 'InitialFileSizeDefined_Log', 
        FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.DENALICTP03MSSQLDATAInitialFileSizeDefined_Log.mdf',
        SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1MB)
GO

We receive the following error:

Msg 1803, Level 16, State 1, Line 2

The CREATE DATABASE statement failed. The primary file must be at least 21 MB to accommodate a copy of the model database.

Per Books On Line (http://msdn.microsoft.com/en-us/library/ms176061.aspx): “The size specified for the primary file must be at least as large as the primary file of the model database.”

Conclusions:

The best part of the day was to summarize my thoughts. We can draw the following conclusions from the above observations:

1. While every database (including the tempdb) is schematically a copy of the model database, the storage & other parameters may differ
2. The minimum size of the any database has to be greater than or equal to that of the model database
   • ALTER DATABASE…MODIFY FILE and CREATE DATABASE cannot be used to circumvent this
3. Extreme caution should be exercised when making changes (schematic or other) that result in a change to the size of the model database

The conclusion #2 is, I believe, by far the most important conclusion of this experiment. I look forward to hearing your thoughts on this topic.

References:

All references today come from Books On Line:

• model database – http://msdn.microsoft.com/en-us/library/ms186388.aspx
• tempdb database – http://msdn.microsoft.com/en-us/library/ms190768.aspx
• ALTER DATABASE File and Filegroup Options – http://msdn.microsoft.com/en-us/library/bb522469.aspx
• CREATE DATABASE – http://msdn.microsoft.com/en-us/library/ms176061.aspx

Until we meet next time,

Be courteous. Drive responsibly.

Based on popular demand from my colleagues and you, the kind reader, I am currently writing a series of posts on the SQL Server Profiler. Five parts have been published till date, and for your kind reference, here are the links to them:

1. What is a SQL Trace? Permissions, space requirements and launching the Profiler
2. Profiler Templates, Template Types & creating custom templates
3. Trace execution options – Save trace to file or a table, Auto-scroll and keyboard shortcuts
4. Review T-SQL code to identify objects no longer supported by Microsoft – Deprecation Event
5. Replaying a trace – from a file or a table – using breakpoints during trace replay

Today, in the last part of the series, we will be looking at another important application of the SQL Server Profiler – Correlating the Profiler Trace with Windows Profiler Log Data. This is helpful when you, as an IT or database administrator suspect that SQL Server is either being held up by or is the cause of an I/O, memory or processor contention.

Windows Reliability & Performance Monitor Log Data

Windows Reliability and Performance Monitor is a Microsoft Management Console (MMC) snap-in that combines the functionality of previous stand-alone tools including Performance Logs and Alerts, Server Performance Advisor, and System Monitor. The tool of interest to us is the Performance Logs and Alerts, erstwhile known as “Perfmon”.

Because this is a post on the SQL Server Profiler, demonstrating the capture of data using the Windows Reliability and Performance Monitor is out of scope. You may refer the References section for more details on the Windows Reliability and Performance Monitor.

For this test, I have created a Performance trace of the following counters (because the goal of this exercise was just to have a demonstration, very basic counters have been chosen. Under “live” circumstances, the counter range would be different):

• Total Disk – Reads/sec
• Total Disk – Writes/sec
• Total Disk – Current Queue Length
• SQL Server Buffer Manager – Page reads/sec
• SQL Server Buffer Manager – Page writes/sec

SQL Server Profiler Trace Data

Using the custom trace developed in earlier editions of this series, capture a SQL Server Profiler Log for the following script. Notice that the script has been designed to have a distinct set of read & write activities:

/**********************************************
           !!!! WARNING !!!!
THIS SCRIPT IS PROVIDED AS-IS AND WITHOUT
WARRANTY. THE AUTHOR AND BEYONDRELATIONAL.COM
ARE NOT RESPONSIBLE FOR ANY DAMANGE CAUSED BY
THE USE OF THIS SCRIPT.
**********************************************/
USE tempdb
GO
CREATE TABLE DefaultTableTest
( DataId INT IDENTITY(1,1),
  RandomId INT DEFAULT (RAND()*1000),
  DataName VARCHAR(10) DEFAULT 'DEFAULT'
)
GO

/******************************************
   Insert some test data into the table
- This should generate lots of writes
******************************************/
USE tempdb
GO
INSERT INTO DefaultTableTest DEFAULT VALUES
GO 20000

/******************************************
   Fetch the test data
- This should generate lots of reads!
******************************************/
USE tempdb
GO
SELECT * FROM DefaultTableTest
GO
SELECT * FROM AdventureWorks2008R2.HumanResources.Employee
GO
--Wait for some time
WAITFOR DELAY'00:00:20'
--Regenerate some activity
USE tempdb
GO
SELECT * FROM DefaultTableTest
GO
SELECT * FROM AdventureWorks2008R2.HumanResources.Employee
GO

It is important to remember that the time ranges for the Windows Performance Monitor Log and SQL Server Profiler Trace log must overlap for the Profiler to be able to correlate them.

Correlating the Profiler Trace with Windows Performance Log Data

Once the data has been collected, now is the time to correlate the Profiler trace with the Windows Performance Log data. Below are the steps that you can use to do so:

In the SQL Server Profiler, open the saved Profiler trace file
On the SQL Server Profiler File menu, click Import Performance Data
In the Open dialog box, select a file that contains a performance log. In the Performance Counters Limit dialog box, select the check boxes that correspond to the System Monitor objects and counters that you want to display alongside the trace. Click OK.
Notice that the performance monitor graph came out to be exactly as expected Lot of Page writes in the beginning Very small period of disk reads and buffer page reads towards the end Note that disk writes continue in an even fashion throughout – indicating disk writing by the SQL Profiler and the Performance monitor
Select an event in the trace events window, or navigate through several adjacent rows in the trace events window by using the arrow keys. The vertical red bar in the System Monitor data window indicates the performance log data that is correlated with the selected trace event.
Click a point of interest in the System Monitor graph. The corresponding trace row that is nearest in time is selected. Once the graph receives the mouse click, you can also use the right & left arrow keys to navigate
To zoom in on a time range, press and drag the mouse pointer (as if you are selecting a section of an image) in the System Monitor graph

NOTES:

1. The time ranges of the Profiler & Windows Performance Log data must overlap
2. For accurate correlation with System Monitor data, the trace must contain both StartTime and EndTime data columns
3. Correlation of the Profiler & Windows Performance Log data is not possible for a running trace that is still active & collecting data

References:

• Windows Reliability & Performance Monitor – http://technet.microsoft.com/en-us/library/cc749249(WS.10).aspx
• Correlate a trace with Windows Performance Log Data – http://msdn.microsoft.com/en-us/library/ms191152.aspx
• All uses of the Profiler – http://msdn.microsoft.com/en-us/library/ms181091(SQL.110).aspx
• Profiler keyboard shortcuts – http://msdn.microsoft.com/en-us/library/ms174164.aspx

I hope you liked reading all 6 parts of my series on the SQL Server Profiler. Do leave your feedback – I really appreciate and thank-you for taking the time out of your busy day to read my posts.

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!

Based on popular demand from my colleagues and you, the kind reader, I am currently writing a series of posts on the SQL Server Profiler. Four parts have been published till date, and for your kind reference, here are the links to them:

1. What is a SQL Trace? Permissions, space requirements and launching the Profiler
2. Profiler Templates, Template Types & creating custom templates
3. Trace execution options – Save trace to file or a table, Auto-scroll and keyboard shortcuts
4. Review T-SQL code to identify objects no longer supported by Microsoft – Deprecation Event

Today, we will be looking at another important application of the SQL Server Profiler – Replay. From Books-On-Line:

“Replay is the ability to save a trace and replay it later. This functionality lets you reproduce activity captured in a trace. When you create or edit a trace, you can save the trace to replay it later.

SQL Server Profiler features a multithreaded playback engine that can simulate user connections and SQL Server Authentication. Replay is useful to troubleshoot an application or process problem. When you identify the problem and implement corrections, run the trace that found the potential problem against the corrected application or process. Then, replay the original trace and compare results.”

To demonstrate replay, let’s the following piece of code, which is rigged to fail. (There is no particular reason why I am using a code that is rigged to fail). I used this piece of code in my posts – Exception handling in T-SQL/TRY…CATCH – Underappreciated features of Microsoft SQL Server and Sunset for RAISERROR and sunrise for THROW – SQL 11 (“Denali”).

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'InnerProc' AND type = 'P')
    DROP PROCEDURE InnerProc
GO

CREATE PROCEDURE InnerProc
AS
BEGIN
    BEGIN TRANSACTION ExceptionHandling
       BEGIN TRY
          PRINT 'In the TRY block of the Inner Procedure...'
          SELECT 1/1

          RAISERROR('An error occured in the Inner procedure.',17,1)  --Line #10 considering CREATE PROC...as Line #1

          COMMIT TRANSACTION ExceptionHandling
       END TRY
       BEGIN CATCH
          SELECT ERROR_NUMBER() AS ErrorNumber
                ,ERROR_SEVERITY() AS ErrorSeverity
                ,ERROR_STATE() AS ErrorState
                ,ERROR_PROCEDURE() AS ErrorProcedure
                ,ERROR_LINE() AS ErrorLine
                ,ERROR_MESSAGE() AS ErrorMessage;

          IF @@TRANCOUNT > 0
             ROLLBACK TRANSACTION ExceptionHandling

          PRINT 'Throwing error from the CATCH block of the INNER Procedure...';   
          --Preceding statement MUST be a semi-colon ';'
          THROW
       END CATCH
END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'OuterProc' AND type = 'P')
    DROP PROCEDURE OuterProc
GO
CREATE PROCEDURE OuterProc
AS
BEGIN
    BEGIN TRY
        PRINT 'In the TRY block of the Outer Procedure...'
        EXEC InnerProc
    END TRY
    BEGIN CATCH
        PRINT 'In the CATCH block of the Outer Procedure...';
        --Preceding statement MUST be a semi-colon ';'
        THROW
    END CATCH
END
GO

--Executing the outer procedure
EXEC OuterProc

Capturing the Replay trace

The SQL Server Profiler ships with a default “TSQL_Replay” trace. For this demo, we will be using this trace type.

As demonstrated in the previous posts of the series, capture the trace for the T-SQL script under review.

Replaying the trace

Open the Trace file saved during the trace collection
Notice the changes to the Profiler toolbar
In case one needs to stop execution at a certain step/statement, select the statement and hit F9 to set a breakpoint
Hit F5 or the yellow arrow key to start the Replay. Apply the required Replay options.
Once the options are set, click on OK. Notice that the trace replays till the breakpoint is hit. Alternatively, one can also use the F10 key to step-through the replay trace one step at a time or the (Ctrl + F10) combination to run to a selected cursor position.

Notice that during the replay options configuration, you can choose whether or not to have the SQL Server blocked process monitor running. This is especially useful if you are trying to replay a trace that you suspect is involved in heavy locking/blocking on your server.

Also notice the status bar, which now shows the number of open connections and the percentage of trace that has finished the replay.

In my next post…

The next post will be the final one of the series, in which I will discuss how to correlate performance counters with the Profiler data.

If you have suggestions on any applications of the Profiler that you want me to cover, drop me a note, and I will try to cover them in future posts.

References

• Replay requirements
• Replay Options
• Considerations for Replaying Traces
• Replay a Trace Table
• Replay a Trace File
• Replay a T-SQL Script
• Replay to a Breakpoint
• Replay a single event at a time

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!

Based on popular demand from my colleagues and you, the kind reader, I am currently writing a series of posts on the SQL Server Profiler. Three parts have been published till date, and for your kind reference, here are the links to them:

1. What is a SQL Trace? Permissions, space requirements and launching the Profiler
2. Profiler Templates, Template Types & creating custom templates
3. Trace execution options – Save trace to file or a table, Auto-scroll and keyboard shortcuts

Today, we will be looking at one of the events that I came across when we were certifying our product against Microsoft SQL Server 2008 when it was originally released. You may want to do the same when certifying your application against SQL 2012 (code named: “Denali”), currently in CTP03. We will see how the Profiler can help us in identifying T-SQL code that would no longer be supported going forward.

Deprecation Announcement Event Class

This Event class can be used to capture events that occur when a feature scheduled to be deprecated in some future release of Microsoft SQL Server is encountered. These features will still be available in the next major release of SQL Server, but may not be available in future releases.

If such code is encountered, it is recommended to consider replacement with suggested replacements.

Deprecation Final Support Event Class

This Event class can be used to capture events that occur when a feature scheduled to be deprecated in the next release of Microsoft SQL Server is encountered. If any such code is encountered, it is definitely time to refactor the code to use the recommended replacements.

An example

Let us intentionally create a scenario that would trigger one of the Deprecation Event classes.

Create a New Profiler Trace
Select the “Deprecation” events from the Event Selection screen
Execute the following T-SQL code against the instance of Microsoft SQL Server being monitored	/* ** Per Books On Line (http://msdn.microsoft.com/en-us/library/ms143729(SQL.110).aspx), ** SET ROWCOUNT for INSERT, UPDATE and DELETE will no longer be supported in the next ** Microsoft SQL Server release. ** Therefore, we expect this to be captured in the Deprecation Final Support Event Class */ USE AdventureWorks2008R2 GO BEGIN TRANSACTION BRDemo SET ROWCOUNT 10 UPDATE HumanResources.Employee SET VacationHours += 1 ROLLBACK TRANSACTION BRDemo
Notice the data captured by the Profiler

In my next post…

Essentially, two major applications of the SQL Server Profiler remain:

1. Correlating Performance data
2. Replaying a trace

We will be discussing these in the next 2 posts, which will be the final ones in the series. If you have suggestions on any applications of the Profiler that you want me to cover, drop me a note, and I will try to cover them in future posts.

References:

• Deprecation Announcement Event Class
• Deprecation Final Support Event Class
• Deprecated Database Engine Features in SQL Server "Denali"
• Deprecated Database Engine Features in SQL Server 2008 R2

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!

Based on popular demand from my colleagues and you, the kind reader, I am currently writing a series of posts on the SQL Server Profiler. The first part was an introduction attempting to answer the question – What is a SQL Trace? Permissions, space requirements and launching the Profiler. The second part dealt with Profiler Templates, Template Types & creating custom templates. Today, we will attempt to answer the following questions:

1. How to “execute” a basic Profiler trace?
2. Which are some of the usability features that may be of use during trace execution?
3. Can the Profiler be manipulated via the keyboard?

Creating a basic Profiler trace

To execute a Profiler trace, the first step is, obviously to launch the Profiler. This can be done by following the steps in my first post – What is a SQL Trace? Permissions, space requirements and launching the Profiler.

Next, go to File –> New Trace to launch the “Trace Properties” window once successful authentication takes place.

General Properties

Trace Information (# 1 and 2)

You can provide a name to the Profiler trace for easier future reference. You can also choose the template type to suit the exercise. For this test though, let’s choose the custom template created in my previous post – Profiler Templates, Template Types & creating custom templates.

Note that depending upon the connection created during authentication, the system shows the Trace Provider name, Provider Type and Version.

Saving the trace output (#3)

Normally, a profiler trace is shown to the user on the grid. However, depending upon the number of events and data columns being captured, there is a performance overhead associated to this method. It is much better instead to dump the trace data to a trace file.

When saving to a file, the user has the option to set a maximum file size to ensure that the file does not grow out of control. When “Enable file rollover” is checked, if the max. file size is reached, the existing file is closed and a new trace file is created. If the “Enable file rollover” is unchecked, the profiler stops capturing events when the max. file size is achieved.

As an alternate to saving trace data to a file, you may also want to dump the data to a table on any Microsoft SQL Server instance. This can be achieved by checking the “Save to Table” checkbox. Also, just as file-sizes can be limited, the number of rows in the trace table can be limited by specifying the max. row count in the “Set maximum rows (in thousands):”.

(Tip: It is advisable to store the trace data to another instance of Microsoft SQL Server to avoid interference with the activity being monitored.)

The “Server processes trace data” must be used with caution. This causes the service running the trace to process trace data instead of the client application. The drawback is that server performance may be affected.

Time-bound tracing (#4)

To have the SQL Server Profiler automatically stop tracing after at certain time, check the “Enable trace stop time” and specify the required stop date and time.

Event Selection

This page allows you to do the following:

1. Review the Events & data columns to capture
2. Add or Remove the Events to capture
3. Add or Remove data columns to capture
4. Apply one or more filters to one or more data columns
5. Specify data columns to Group the results on and also specify the order in which data columns should be shown to the user
6. Brief description of the event class selected (changes when event class selection changes)
7. Brief description of the data column (changes as the user hovers over the data column)

A step-by-step guide on customizing the template using the controls specified by #2, 3, 4 and 5 is available in my previous post – Profiler Templates, Template Types & creating custom templates.

Executing a trace

Once the Profiler trace is defined, one just needs to click on the “Run” button in the “Template Properties” window. The SQL Server Profiler will now start showing events to the user depending upon the chosen template.

1. Basic controls – start, pause & stop
2. Launch SQL Server Management Studio
3. Launch the Performance Monitor
4. Clear the trace data grid
5. Review the trace properties (The “Trace Properties” window opens, but in read-only mode if the trace is running or is paused)
6. Find a string within any of the data columns

Auto-scroll – a nice usability feature

Most of the usability features (launching of SSMS, PerfMon and the flexibility of the “Find” window) are quite straight-forward. However, there is one feature that I like the most. Look at the toolbar on the profiler, and note the icon with the grid and a little, blue down arrow:

This icon is the “Auto-scroll” button. When selected, the grid scrolls to the most recently collected data row as more and more data starts collecting on the grid. When not selected, the grid needs to be scrolled manually.

Typically, if I am trying to capture and analyze a trace for a particular database and host and want to see things as they happen, I prefer the enable “Auto-scroll”. If I am saving data to a file or a table, I would most probably be looking at the data later on and would therefore have the “Auto-scroll” turned off.

Can the Profiler be Manipulated via the Keyboard?

Of course, it can! Here’s where you can find a list of all keyboard shortcuts – http://msdn.microsoft.com/en-us/library/ms174164.aspx

In my next post…

SQL 2012 is just around the corner. Most of the community members are probably evaluating SQL 2012. For those who are not yet ready, but will ultimately move to SQL 2012 must most definitely be moving to SQL Server 2008 R2. We will see how to use the Profiler to identify which objects are in most dire need of replacement for certification against a release of SQL Server.

We would be approaching the end of the series and would therefore start looking at other scenarios where the Profiler can be used.

References

• Keyboard Shortcuts
• How to create a trace (SQL Server Profiler)?

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!