If you work for a product based organization, you would know that databases created in versions as old as SQL 7 are still around. The only thing is that over time, they have evolved via upgrade programs which have upgraded them to the latest and greatest levels – running on latest editions such as Microsoft SQL Server 2008 R2.

Whenever you work with a production database for troubleshooting or for an upgrade, have you ever wondered about the SQL Server version in which a particular database created? Answer to this question would answer questions such as:

1. How old is this database?
2. Has this database ever been reconstructed? (Reconstruction meaning data from an existing database pumped to a new database)

Getting answers to these questions is not that difficult, provided you can run a DBCC command against a database. All that needs to be done is:

DBCC DBINFO('AdventureWorks2008R2') WITH TABLERESULTS

That’s it! Well, let’s dissect the DBCC command that we used:

DBINFO: This, to the best of my knowledge, is an undocumented DBCC procedure. This means that it does not come with any sort of warranty from Microsoft (and also none from this site and me). It is therefore advised not to use this in production environments.

Next up, as the name suggests, DBINFO provides “insider” information about the database – something that is not available regularly via viewing the database options or via any DMV. The details of each and every result option is not in scope of this discussion. You may want to follow posts on SQLSkills [Paul Randall (blog)] for insights into DBCC from the guru himself.

TABLERESULTS: Most of us would have used DBCC CHECKDB to verify the integrity of our database. We get a whole lot of information as the output. However, not all DBCC commands return an output. The various output formats for DBCC that I know of are:

1. Output to client (i.e. – display “on screen” as is the case with DBCC CHECKDB)
2. Log to SQL Server Log
3. Provide output via the use of TRACE flag – 3604 (When researching this post, I could not find documentation on MSDN for this flag. In such cases, please follow the assumption I take – it’s undocumented, and therefore not covered by warranty)
4. In addition, we have a 4th output format – output as a table. The WITH TABLERESULTS clause specifies the results in a tabular format that can be loaded into a table.

By default DBCC DBINFO does not return any results. Therefore, we either need to use option #3 (TRACE Flags) or #4 to display the output of the DBINFO command. In the query I have mentioned above, we used the TABLERESULTS option.

The result

When I ran the above mentioned query against the AdventureWorks2008R2 sample database for Denali CTP03, the following was the output:

If we look at the fields – dbi_createVersion and dbi_crdate, we can see that they indicate the SQL Server version in which the database was created and creation date/time respectively.

For the AdventureWorks2008R2 for SQL 11 (“Denali”) CTP03, the values are:

• dbi_createVersion = 679
• dbi_crdate = 2010-08-02 17:26:22.770

So, now we know that this database was upgraded from another version of Microsoft SQL Server (dbi_createVersion is different when compared to dbi_version). Also, we know that the database was created first in August, 2010.

I trust that the above information will be helpful to you sometime in the future.

Until we meet next time,

Be courteous. Drive responsibly.

For anyone who has run Microsoft SQL Server under a domain account, the first thing that comes to mind upon thinking about changing the account or the account password is planning for downtime. However, starting Microsoft SQL Server 2008, this does not have to be the case.

Microsoft SQL Server 2005 and below

In Microsoft SQL Server 2005 and below, changing the account password for the Microsoft SQL Server service normally involved:

1. Launching Microsoft Management Console (MMC)
2. Adding the Services snap-in (services.mmc)
3. Navigate out to the SQL Server service
4. Change the domain account and/or the account password
5. Repeat the same for all dependent services (like the Full Text Search service, etc)
6. Restart the SQL Server & dependent services

Restarting the SQL Server means downtime for your applications – a message which no administrator wants to take to the rest of the organization.

Microsoft SQL Server 2008 and above

Starting Microsoft SQL Server 2008, this is no longer the case. If the above steps are followed, then one might end up with at least one of the following issues:

1. Windows Registry permissions are not properly – this prevents the service running under the new account from reading SQL server settings
2. A service restart is required for the changes to take effect

If the following steps are followed, then the Microsoft SQL Server service does not need to be restarted and the appropriate Windows Registry permissions also set automatically.

The right way…

1. Go to Start –> All programs –> Microsoft SQL Server <version>
2. Go to Configuration Tools
3. Click on “SQL Server Configuration Manager”
4.  
   • Alternatively, you can run the following on the “Run” prompt:
     • SQLServerManager11.msc – For Denali CTP03
     • SQLServerManager10.msc – For SQL Server 2008
5. Within the SQL Server Configuration Manager, go to the “SQL Server Services” node
6. In the right-hand side pane, you will see all the SQL Server services listed
   1. Simply right-click on the required SQL Server Service and go to “Properties”
   2. 
   3. Next, change the domain service password and click OK
   4. 
7. Once the password has been changed – there is no need for the SQL Server service to be restarted

If ever you need a service restart

Let’s say you need a service restart because of a configuration change. Instead of heading over to the Services.msc console, one should be using one of the following options:

Using SQL Server Configuration Manager

1. Right click on the SQL Server Service
2. Click on “Restart”
3. 

Using SQL Server Management Studio

1. In the Object Explorer pane, connect to the server in question
2. Right-click the server name and click “Restart”
3. 
4. Click on “Yes” on the following Message Box that comes up (beware, this message box might appear in the background)
5. 

My observation

Old habits die hard. I have seen a lot of professionals (developers, especially) who still use the Services snap-in to manipulate SQL Server permissions and to restart these services. It is my recommendation to them to make a conscious effort to use the SQL Server Configuration Manager for these tasks.

For more reference on how to setup Windows Service accounts for Microsoft SQL Server, please refer: http://msdn.microsoft.com/en-us/library/ms143504.aspx

Until we meet next time,

Be courteous. Drive responsibly.

DBCC DROPCLEANBUFFERS is a very familiar DBCC command in use during performance tuning exercises. We have always used it to test queries with a cold buffer cache without shutting down and restarting the server. But, did you know that DBCC DROPCLEANBUFFERS alone may not be sufficient? Did you know that it is important to ensure that the transaction log is “cleared” before cleaning the buffers?

I learnt it from the Books On-Line here, did not quite believe it and decided to do my own little test, which I will demonstrate today.

Edit (October 03, 2011, 14:00 US-EDT): Please do not forget to refer an alternate experiment towards the end of the post. The additional experiment is based on the feedback received from my kind friend, Chintak Chhapia (blog).

Some important points…

Before we begin, I would like to mention a few important points:

1. If the first two lines seem alien to you, it is time to take a step back and understand the basics of SQL Server operation – especially transaction log and buffer pools
2. The queries and methods provided by this article should not be used in production environments. These queries and procedures are provided “as-is” and without warranty
3. Please do not use the queries and methods provided by this article without consultation and supervision by a DBA

Preparing the test

First of all, let’s create a sample database, and then change the recovery model to SIMPLE. This would allow the transaction log to be cleared when we issue a CHECKPOINT, and we would not have to run a transaction log backup. Note that after changing the recovery model, it is recommended to take a full database backup. I assume that the server where we are creating this database also contains the AdventureWorks2008R2 database for creation of sample data.

USE master
GO
CREATE DATABASE CleanBufferTest
ON PRIMARY
   (NAME = 'CleanBufferTest_Data',
    FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.DENALICTP03MSSQLDATACleanBufferTest_Data.mdf',
    SIZE = 10,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10)
LOG ON (NAME = 'CleanBufferTest_Log',
        FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.DENALICTP03MSSQLDATACleanBufferTest_Log.ldf',
        SIZE = 10,
        MAXSIZE = UNLIMITED,
        FILEGROWTH = 10)
GO

ALTER DATABASE CleanBufferTest SET RECOVERY SIMPLE;
GO

Using only DBCC DROPCLEANBUFFERS

Let’s create a test table and select data from it.

USE CleanBufferTest
GO
--Insert data from the AdventureWorks2008R2 database
SELECT * INTO CleanBufferEmployees FROM AdventureWorks2008R2.HumanResources.Employee
--Select data from the newly created table
SELECT * FROM CleanBufferEmployees

Now, using the below mentioned DMV based query, let’s see what data pages are available in the buffer.

USE CleanBufferTest
GO
--DMV to view the buffer descriptors
select so.name,* 
from sys.dm_os_buffer_descriptors obd
INNER JOIN sys.allocation_units au ON obd.allocation_unit_id = au.allocation_unit_id
INNER JOIN sys.partitions part ON au.container_id = part.hobt_id
INNER JOIN sys.objects so ON part.object_id = so.object_id
WHERE obd.database_id = DB_ID()
AND so.is_ms_shipped = 0

Check the results of the query mentioned above.

Run DBCC DROPCLEANBUFFERS and then re-run the DMV based buffer look-up query:

DBCC DROPCLEANBUFFERS

The following was the output in my case. As you can see, our buffers are NOT clean!

Finally, clean up the test bench.

DROP TABLE CleanBufferTest

The ideal method to clean buffers

Let’s re-run the above test, but with this time, we have the CHECKPOINT in between to flush the transaction log to disk.

USE CleanBufferTest
GO
--Insert data from the AdventureWorks2008R2 database
SELECT * INTO CleanBufferEmployees FROM AdventureWorks2008R2.HumanResources.Employee
--Select data from the newly created table
SELECT * FROM CleanBufferEmployees
GO

--Checkpoint and then DROPCLEANBUFFER
CHECKPOINT
DBCC DROPCLEANBUFFERS


USE CleanBufferTest
GO
--DMV to view the buffer descriptors
select so.name,* 
from sys.dm_os_buffer_descriptors obd
INNER JOIN sys.allocation_units au ON obd.allocation_unit_id = au.allocation_unit_id
INNER JOIN sys.partitions part ON au.container_id = part.hobt_id
INNER JOIN sys.objects so ON part.object_id = so.object_id
WHERE obd.database_id = DB_ID()
AND so.is_ms_shipped = 0
GO

As you can see, if DBCC DROPCLEANBUFFERS are used with a CHECKPOINT, Microsoft SQL Server forces all dirty pages for the current database to be written to disk and then cleans the buffers – resulting in a truly cold buffer cache. This recommendation has been made on the Books On Line page – http://technet.microsoft.com/en-us/library/ms187762.aspx

Edit (Added – October 03, 2011, 14:00 US-EDT)

As Chintak Chhapia (blog) points out in his comment below, the CHECKPOINT was required because we had a dirty page due to the SELET…INTO operation which ultimately is an INSERT operation.

In a collaborative environment, one would probably end up tuning stored procedures that perform UPDATEs after a couple of SELECTs or when running the application and generating test data in parallel. In such cases too, you would end up with dirty pages in the buffers. Let’s run a simple UPDATE on the CleanBufferEmployees table and then run DBCC DROPCLEANBUFFERS.

USE CleanBufferTest
GO
UPDATE CleanBufferEmployees SET VacationHours += 1
GO

--Attempt to clean buffers
DBCC DROPCLEANBUFFERS
GO

When we run our query to look at the buffers, we encounter a couple of rows in the result set, indicating that the buffers are unclean, and therefore requires a CHECKPOINT before DBCC DROPCLEANBUFFERS:

Until we meet next time,

Be courteous. Drive responsibly.

The debugger in the SQL Server Management Studio (SSMS) for the upcoming SQL 11 (“Denali”) is packed with exciting features.The new SSMS is a derivative of the VS2010 shell and therefore extends the same debugging experience (with the obvious differences related to technology) that it would deliver to let’s say a C# developer.

We have already discussed a couple of debugging enhancements. If you have missed any of them, or would like a refresher, please use the links below:

• Setting, Labeling, Using & Searching Conditional Breakpoints
• Export/Import Breakpoints
• “When Hit” do something
• An Introduction to Data Tips

We will be continuing the example that we used last time after a bit more simplification:

DECLARE @iCounter INT = 0

WHILE (@iCounter <= 20) 
BEGIN
   PRINT 'Hello World! The counter reads: ' + CAST(@iCounter AS VARCHAR(2)) 

   SET @iCounter += 1 
END
GO

Data Tips

As I mentioned in my previous post, the Data Tips are a very light-weight version of the Watch window. They allow you to view the value of variable within the current scope by placing the mouse pointer over the given variable when in break/debug mode.

Editing data within a data-tip

One can change the value of a variable at runtime from within the watch window to force the debugger to use that value instead of the normally computed value. Because the Data tips are a light-weight version of the Watch window, editing data in a data-tip should be possible.

Start a debug session
To edit the value in a data-tip, click on the "value" area	*For read-only variables, this will not be editable
Type in the required value
Allow the execution to continue

Preservation of data tips after debug session is over

Let’s assume that after an hour or so of debugging, you identify a variable taking on an incorrect value, which you need to protect against. You stop the debugger, but forget to note down the offending value! Simply the thought of spending another hour debugging would give you a headache. Well, not if Data Tips are used. That’s because they hold the value even after the debugging session is over.

All you need to do is to hover the mouse over the “pin” icon in the indicator pane and you will see that the data-tip containing the value from the previous session opens up with a balloon help saying “Value from last debug session”.

Exporting & Importing Data Tips

Exporting & Importing Data tips is very similar to exporting & importing breakpoints. In fact, they even share the same limitation – upon import, the file path and name must be the same as it was when the export was performed.

Go to: Debug –> Export Datatips
Save the DataTips as an XML file
To Import, simply go to Debug –> Import DataTips and import the XML file

Know more

To know more about how to use DataTips, please refer the MSDN article – http://msdn.microsoft.com/en-us/library/ea46xwzd.aspx

My Ranking on the Debugger enhancements

The time has now come to arrange the debugger enhancements in order of my preference. So, here goes:

1. Availability of DataTips & ability to pin/move them
2. Creating conditional breakpoints & labeling them
3. Searching for breakpoints based on their label
4. “When Hit” do something
5. Editing data within DataTips
6. Preservation of DataTips after a debug session
7. Export/Import of breakpoints & DataTips

What would be your ranking? Do let me know.

Until we meet next time,

Be courteous. Drive responsibly.

The debugger of any IDE is a very powerful tool because it gives developers the insight on how their program or script is functioning. I have throughout been a SQL person, and have always felt that at some stage, Microsoft SQL Server did not deliver at par with it’s Visual Studio counterparts. This was despite the fact that the SQL Server Management Studio (SSMS) was a derivative of Visual Studio.

However, with the upcoming release of SQL 11 (“Denali”), I have no complaints. The new SSMS is a derivative of the VS2010 shell and therefore extends the same debugging experience (with the obvious differences related to technology) that it would deliver to let’s say a C# developer. The Visual Studio debugger provides a variety of tools for inspecting and modifying the state of a script. Obviously, these tools function only in break/debug mode.

We have recently been discussing about a couple of features related to Breakpoints. If you missed them or would like a refresher, you can click on the respective links below:

• Setting, Labeling, Using & Searching Conditional Breakpoints
• Export/Import Breakpoints
• “When Hit” do something

Today, we will be looking at something called “Data Tips”. We will be continuing the example that we used last time after a bit more simplification.

DECLARE @iCounter INT = 0

WHILE (@iCounter <= 20)
BEGIN
    PRINT 'Hello World! The counter reads: ' + CAST(@iCounter AS VARCHAR(2))

    SET @iCounter += 1
END
GO

Data Tips

Per MSDN, “DataTips are one of the most convenient tools for viewing information about the variables and objects in your program during debugging. When the debugger is in break mode, you can view the value of a variable within the current scope by placing the mouse pointer over the variable in a source window.”

I like to think of it as a very light-weight version of the Watch window. When debugging, almost any troubleshooter likes to be in full control – knowing exactly what is going on where. If a variable changes it’s value, it might be important because it may not be warranted and might produce incorrect results. Data Tips help us in being “aware” about our surroundings, and at times, in controlling the flow of execution.

Please note that to the best of my knowledge, Data Tips are available in all the flavours of Visual Studio 2010.

To Display A Data Tip

Start a debug session
However the mouse over the variable to be evaluated, a Data Tip appears

To Move, Pin and Unpin A Data Tip

Click the “pin” icon
The data-tip is now “pinned”
Drag the data tip to the desired location to move it
Hover over the data-pin and click the “pin” icon again to un-pin the data-tip

Adding Comments to a Data Tip

Click the “Expand” arrow on the data-tip
Add required comments in the edit box
Click anywhere on the “Canvas”

To Close all Data Tips

We have not one, but 3 distinct options to clear out the data-tips on a Microsoft SQL Server Query Editor window.

Option #1 – Debug Menu
Option #2 – The Data tip itself
Option #3 – Right-click the “indicator” column

Later this week, we will see:

• Preservation of data tips after debug session is over
• Editing data in a data-tip
• Exporting & Importing data-tips

Finally, I will summarize which of the features I think I will be using the most once SQL 11 (“Denali”) hits production.

Until we meet next time,

Be courteous. Drive responsibly.