Category Archives: SQL

SQL 2008 Hyper-Threading Support

Ok, this is a somewhat misleading post, thus far.  I’m struggling to uncover support (in either direction) if SQL 2008 has improved support for hyper threading – and I’d like to use this medium to get any feedback YOU may have, so please reply in the comments below!?

Now, I’ve been around long enough to know, the easy (and often correct) answer is always “It Depends” but at this point I’m simply looking for any kind of supporting documentation about HT within SQL 2008, all searches have turned up minimal results, most dating back many years – so it’s time we update content on the subject.

I promise to update this post with any supporting data or links I find, and hope it may help you down the road – so if you have something to share – please leave a reply!

*UPDATE.  While it seems SQL 2008 hyper-threading MAY work for your environment, it posed numerous issues in ours.  Issues were primarily related to I/O, or thread in-efficiency causing lots of waits (CXPacket) due to timing of true CPU threads vs. HT CPU threads.  We experienced numerous timeouts and parallelism issues with query performance – all of which were resolved by DISABLING hyper-threading for SQL 2008.  Additionally, if you’re using newer Nehalem processors, you should also confirm “TURBO” mode is disabled, which allows frequency shifting among cores, which can cause similar threading problems for heavily used OLTP database systems.

So, while HT and “Turbo” are innovative technologies, it remains once again, use caution implementing in your environments.  These are probably fine in traditional application workloads, but may not be in SQL and/or Exchange environments.  Hope this helps save you time troubleshooting.

Advertisements

Find Missing SQL 2005 Indexes

The following script helps you to find the top 10 missing indexes using SQL 2005 DMV.

SELECT TOP 10 migs_adv.index_advantage,
mid.database_id,
mid.object_id,
mid.statement as table_name,
mig.index_handle as index_handle
FROM  (select (user_seeks+user_scans) * avg_total_user_cost * (avg_user_impact * 0.01) as index_advantage, migs.*
from sys.dm_db_missing_index_group_stats migs) as migs_adv,
sys.dm_db_missing_index_groups mig,
sys.dm_db_missing_index_details mid
WHERE migs_adv.group_handle = mig.index_group_handle
AND mig.index_handle = mid.index_handle
ORDER BY migs_adv.index_advantage DESC

Now take the index handle of the top missing index and apply below to determine which columns should be included in the newly created index:

SELECT * FROM sys.dm_db_missing_index_details where index_handle = 45

Once you’ve got your columns, create your new index: (we tend to put indexes in their own fileGroups so they may reside on their own disk spindle for increased performance)

CREATE INDEX missingIndex_xxx
ON [DB].[dbo].[TBL]([col1], [col2], [col3])
INCLUDE ([col4], [col5])
ON FG_XXX_Index;

Find Unused SQL 2005 Indexes

SELECT o.name AS object_name, i.name AS index_name
   , i.type_desc, u.user_seeks, u.user_scans, u.user_lookups
, u.user_updates, u.last_user_seek, u.last_user_scan
, 'Drop index ' + i.name + ' on ' + o.name as DropIndexStatement
FROM sys.indexes i
JOIN sys.objects o ON  i.object_id = o.object_id
LEFT JOIN  sys.dm_db_index_usage_stats u ON i.object_id = u.object_id
          AND    i.index_id = u.index_id
          AND    u.database_id = DB_ID()
WHERE    o.type <> 'S'
and isnull(u.user_updates,0) > 0
and i.type_desc <> 'HEAP'
ORDER BY    (convert(decimal(19,4),ISNULL(u.user_seeks, 0))
+ ISNULL(u.user_scans, 0)
+ ISNULL(u.user_lookups, 0))/ISNULL(u.user_updates, 0) asc
, user_updates desc, o.name, i.name

The results of this will show you how many times each index has been used, and how often it’s been updated.  If you have 0 seeks, scans, and lookups but a ton of updates, it’s a good bet that the index in question is a waste of time and can be deleted. Conversly, if you have an index that has lots of seeks/scans and minimal updates – it’s really earning it’s paycheck.

The query even includes the DROP command as part of the results, so all you need to do is cut, paste, and execute.

New t-SQL features for SQL 2k8


Inline Variable Assignments

When wearing my developer hat, I spend a lot of time writing stored procedures and playing around with variables. Since I also spend plenty of time programming I’ve always been a bit jealous of the ability to new-up a variable in C# (or VB.NET) to a specified value. For example, prior to SQL Server 2008, if I wanted to create a new datetime variable and set it equal to right now, I’d have to do that in two steps, like so:

DECLARE @now datetime

SET @now = GETDATE()
SELECT @now [now]

But with SQL Server 2008, I can do that in a single line of code, as follows:

DECLARE @now datetime = GETDATE()

SELECT @now [now]

This minor change helps improve code readability by letting you instantiate variables and assign their initial value inline while simultaneously providing a minor productivity boost as well.

Row Constructors

MySQL has two features that many db pros want SQL Server to adopt. The first is the dump function, which allows you to dump an entire table or database to a DDL script along with supporting DML INSERT scripts that make it easy to persist an entire table or database to a single text file. This file can then be executed on another server to recreate any output tables and repopulate them with data by executing the accompanying INSERT statements.

The second feature that is frequently referenced that SQL Server could borrow from MySQL is what some have called Bulk Inserts or the ability to INSERT multiple, static, rows with a single INSERT statement. For example, if I’m creating a simple table defining user types and I want to populate it with some initial values, each value will require its own INSERT statement like so:

CREATE TABLE UserTypes (

UserTypeId tinyint IDENTITY(1,1) NOT NULL,

UserType varchar(20) NOT NULL

)

INSERT INTO UserTypes (UserType)

VALUES (‘User’)

INSERT INTO UserTypes (UserType)

VALUES (‘Manager’)

INSERT INTO UserTypes (UserType)

VALUES (‘Admin’)

I’ve always been a bit lazy when populating tables in this fashion and have gotten into the habit of just SELECTing rows into my table using a UNION, mostly because I hate typing VALUES and open/close parenthesis:

INSERT INTO UserTypes (UserType)

SELECT ‘User’

UNION SELECT ‘Manager’

UNION SELECT ‘Admin’

But SQL Server 2008 has caught up with MySQL, and I can now insert multiple rows within a single INSERT statement with T-SQL 2008’s new Row Constructors:

CREATE TABLE UserTypes (

UserTypeId tinyint IDENTITY(1,1) NOT NULL,

UserType varchar(20) NOT NULL

)

INSERT INTO UserTypes (UserType)

VALUES (‘User’),(‘Manager’),(‘Admin’)

Again, this isn’t a huge or earth-shattering change, but a very nice improvement that can really help boost usability and increase productivity. (Too bad there’s no sensible way to ditch those parenthesis though.)

Compound Assignment Operators

Another great, but subtle, improvement to T-SQL in SQL Server 2008 is the inclusion of new Compound Assignment Operators (though Microsoft just calls them Compound Operators). Like the other improvements mentioned, these aren’t life-savers by any stretch of the imagination, but they do make some interactions easier, especially if you’re comfortable with the greater range of assignment operators available in C# (and VB.NET ).

For example, prior to SQL Server 2008, if you wanted to append text or increment a variable, you needed to do it in a rather verbose manner, as follows:

— declare:

DECLARE @int int

DECLARE @string varchar(10)

— initialize:

SET @int = 5

SET @string = ‘123’

— increment:

SET @int = @int + 5

SET @string = @string + ‘456’

SELECT @int, @string

But with SQL Server 2008, you can do it a bit more logically and more concise:

— declare and init:

DECLARE @int int = 5

DECLARE @string varchar(10) = ‘123’

— increment:

SET @int += 5

SET @string += ‘456’

SELECT @int, @string

These new assignment operators work with all of the standard mathematical assignment operators including bitwise operations which can help make interacting with bitmaps a bit easier as well.

Making Your Life Easier

Ultimately, all three of these new additions are rather minor when compared to things like Full Text Integration, sparse columns, FILESTREAM storage, Table-Valued Parameters, and a host of other improvements and additions. But they all represent subtle improvements that can really help to both improve productivity and ease usability, so they’re definitely worth adding to your repertoire.

SQL 2005 Cluster Startup Params

Having recently deployed a new SQL 2005 cluster, I thought I’d share a couple things that I originally got hung up on. 

First, startup parameters, It took me some time to find that when more than one parameter is used as Startup Parameters in SQL Sever, they should be separated by semicolon rather than space.

For example:
-dC:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\mastlog.ldf;-T4616

Other, BIG lesson learned, is when moving SQL 2005 System Databases, you’re again required to update startup params.  If you happen to make a mistake, there’s a small hoop you have to jump through to remedy the problem, as simply fixing the startup parameters won’t recover the SQL Service, as it simply won’t restart.  In a clustered environment, when you change a protocol parameter for a clustered instance of Microsoft SQL Server 2000 or 2005 to a value that is not valid, the SQL Server service cannot start. Additionally, you cannot change the protocol parameter back to the original value.

This problem occurs because the cluster service obtains an incorrect protocol setting from a cluster checkpoint when you start the SQL Server service.  Use the steps below for a work around:

  1. Bring the SQL Server resource offline by using Cluster Administrator.
  2. At a command prompt, run one of the following commands to disable the cluster checkpoint for the specific registry subkey:
    For an instance of SQL Server 2005, run the following command:

    cluster res “SQL Server” /removecheck: “Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER”

    Note In this command, MSSQL.x is a placeholder for the instance ID for the instance of SQL Server. You can determine the corresponding value for the system from the value of the MSSQLSERVER registry entry in the following registry subkey:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\
    For a default instance of SQL Server 2000, run the following command:

    cluster res “SQL Server” /removecheck: “Software\Microsoft\MSSQLServer\MSSQLSERVER”
    For a named instance of SQL Server 2000, run the following command:

    cluster res “SQL Server” /removecheck: “SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\MSSQLSERVER”

    Note In this command, InstanceName is a placeholder for the name of the SQL Server 2000 instance.

     

  3. Correct the protocol parameter for the clustered instance of SQL Server on all nodes.
  4. At a command prompt, run one of the following commands to enable the cluster checkpoint for the specific registry subkey:
    For an instance of SQL Server 2005, run the following command:

    cluster res “SQL Server” /addcheck: “Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER”
    For a default instance of SQL Server 2000, run the following command:

    cluster res “SQL Server” /addcheck: “Software\Microsoft\MSSQLServer\MSSQLSERVER”
    For a named instance of SQL Server 2000, run the following command:

    cluster res “SQL Server” /addcheck: “SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\MSSQLSERVER”

     

  5. Bring the SQL Server resource online.

Happy Recovery!

Moving SQL 2005 System Databases

While in the process of upgrading our database servers, I had to recall how to move system databases, which forced me to update my notes, from sql 2000.  Below are the steps required to move sql 2005 system databases.  If you’re wondering why this is needed, well there’s a few reasons.  First, if you don’t select the target location during SQL install, these files can be put under the default installation path, typically under the C:\ drive.  In our world, optimization is everything, therefore we attempt distribute data, logs, tempDB on their own spindles with varying RAID levels to maximize performance, which is a whole other topic.

NOTE:  These steps are slightly different if you’re working with clustered databases, which I’ll speak to in a future post.

MODEL:
To move the MODEL database, you must start SQL Server together with the -c option, the -m option, and trace flag 3608. Trace flag 3608 prevents SQL Server from recovering any database except the master database.

1. In SQL Server Configuration Manager, click SQL Server 2005 Services.

2. In the right pane, right-click SQL Server (<instance_name>), and then click Properties.

3. On the Advanced tab, in the Startup Parameters box, type the parameters separated by semicolons (;). i.e. -c; -m; -T3608

For example, to start in single-user mode, insert -m; in front of the existing startup options, and then restart the database.

Important: After you are finished using single-user mode, you must remove the -m; from the Startup Parameters box before you can restart the server instance in the normal multi-user mode.

4. Stop and then restart SQL Server.

5. Detach the model database by using the following commands:

use master
go
sp_detach_db ‘model’
go

6. Move the Model.mdf and Modellog.ldf files from the D:\Mssql7\Data folder to the desired location.

7. Reattach the model database by using the following commands:

use master
go
sp_attach_db ‘model’,’R:\sqlData\model.mdf’,’T:\sqlLogs\modellog.ldf’
go

8. Remove -c -m -T3608 from the startup parameters in SQL Server Configuration Manager.

9. Stop and then restart SQL Server. You can verify the change in file locations by using the sp_helpfile stored procedure. For example, use the following command:

use model
go
sp_helpfile
go

 

MSDB:
To move the MSDB database, you must start SQL Server together with the -c option, the -m option, and trace flag 3608. Trace flag 3608 prevents SQL Server from recovering any database except the master database. To add the -c option, the -m option, and trace flag 3608, follow the steps in the “Moving the model database” section. After you add the -c option,  the -m option and trace flag 3608, follow these steps:

1. Stop, and then restart SQL Server.

2. Make sure that the SQL Server Agent service is not currently running.

3. Detach the msdb database as follows:

use master
go
sp_detach_db ‘msdb’
go

4. Move the Msdbdata.mdf and Msdblog.ldf files from the current location to the new target location.

5. Remove -c -m -T3608 from the startup parameters in SQL Server Configuration Manager.

6. Stop and then restart SQL Server.

Note If you try to reattach the msdb database by starting SQL Server together with the -c option, the -m option, and trace flag 3608, you may receive the following error message:

Server: Msg 615, Level 21, State 1, Line 1 Could not find database table ID 3, name ‘model’.

7. Reattach the msdb database as follows, changing “X” for your target location:

use master
go
sp_attach_db ‘msdb’,’X:\sqlData\msdbdata.mdf’,’X:\sqlLogs\msdblog.ldf’
go

 

MASTER: Moving the master database

1. Change the path for the master data files and the master log files in SQL Server Configuration Manager. Note You may also change the location of the error log here.

2. Right-click the SQL Server in Enterprise Manager and then click Properties.

3. Click Startup Parameters to see the following entries:

-dX:\data\master.mdf
-eY:\errlog\ErrorLog
-lZ:\logs\mastlog.ldf

-d is the fully qualified path for the master database data file.
-e is the fully qualified path for the error log file.
-l is the fully qualified path for the master database log file.

4. Change these values as follows:

a. Remove the current entries for the Master.mdf and Mastlog.ldf files.

b. Add new entries specifying the new location:

-dX:\SQLDATA\master.mdf
-lZ:\SQLDATA\mastlog.ldf

5. Stop SQL Server.

6. Copy the Master.mdf and Mastlog.ldf files to the new location (E:\Sqldata).

7. Restart SQL Server.

tempDB: Moving the tempdb database

You can move tempdb files by using the ALTER DATABASE statement.

1. Determine the logical file names for the tempdb database by using sp_helpfile as follows:

use tempdb
go
sp_helpfile
go

The logical name for each file is contained in the name column. This example uses the default file names of tempdev and templog.

2. Use the ALTER DATABASE statement, specifying the logical file name as follows:

use master
go
Alter database tempdb modify file (name = tempdev, filename = ‘S:\tempDB\tempdb.mdf’)
go
Alter database tempdb modify file (name = templog, filename = ‘T:\sqlLogs\templog.ldf’)
go

You should receive the following messages that confirm the change:

Message 1
The file “tempdev” has been modified in the system catalog. The new path will be used the next time the database is started.

Message 2
The file “templog” has been modified in the system catalog. The new path will be used the next time the database is started.

3. Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.

4. Stop and then restart SQL Server.

 

– Hope this helps save you some time next time you need to move system databases around.

Hero’s Happen Here – Detroit Launch

Attended Microsoft’s Hero’s Happen Here launch event today, in downtown Detroit.  Each “launch” since windows XP seems to be less and less of an event.  Sure, Microsoft knows how to host an event, logistics, registration, even swag – its pretty good.  However, my biggest critisism comes to the lack-luster presentations, as there just doesnt seem to be much that fires up the crowd(s).

Ok, yes, i know, its a technology conference, yet there’s a different feel to conferences in the midwest, not sure if vendors lower the bar, or the audience itself is to blame?  Firstly, there’s the ongoing issue, that not a single “demo” works – its actually become comical, somebody, somewhere, put some accountability on presenters to showcase demos that actually work!

I found myself sitting in the SQL 2k8 track most the day, as it was the area i knew the least about, win2k8 & vs2k8 has been covered pretty well in recent events and the press, and both, to me, represent evolutionary, not revolutionary releases.  Sure there’s some cool new stuff, but nothing that makes me race back to the office and start playing with the bits (quite possibly because, we’ve already been doing some experimenting – but still) – so I was anxcious to get under the hood of sql 2k8, and after several hours, this release also seems consistent with my previous assessment.  I’ll be posting my notes in the next couple days, for those interested.

One thing not covered at all, which is still odd to me, is Service Broker.  Is anybody using it besides us?  It’s easily the most under-utilized feature (originally surfaced in sql 2k5), which after a brief chat at the experts-panel, sounds like it may soon be getting some much-deserved attention, now that micorosoft has officially pulled the plug on notification services.  In any event, I’d give this event a 4, on my 1-10 scale, but am thankful for the swag (a msft lunch box & copies of: vista/visual studio/windows 2k8 and latest sql bits (as sql isnt yet shipping.)