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.

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.)

SQL Server Database Backups for the enterprise – Part 1

I’ve been working with our team for a while to continually refine our db backup procedures.  We’ve got a fairly complex database environment, with quite a few sql servers in various locations, which prior to SQL SP2 we did backups primarily thru scheduled nightly jobs.  An update in SP2 broke our (basic) model, which re-energized my dreams of db backup automation and a more granular, manageable process. 

 Well, I’m nearly 90% complete with my new model – check back soon for Part 2, where I’ll offer my scripts for download.  Highlights include a single table that allows you to manage your backup job.  This table (briefly) includes the “databases” you want to backup, the backup engine to use (whether local or quest, red-gate, idera, etc), the schedule for backing up databases, including log backups and even file-group backups.  Next I wrote a Stored Proc which loops thru this table looking for backup jobs to run, using the respective backup engine.  The table/SP also includes a feature to write copies of the backup to both the local DB as well as a remote NAS device (or any path you provide in the setup table.) One nice feature about this is you can keep the past 7 days backups local on the machine, in the event you need to do a quick restore, whereas backups older than 7 days get archived to a NAS device, still recoverable, but depending on the location may take additional time to recover.  This helped us to address storage space, both locally and remotely, managing encryption and compression during the process.  While the scope of this little backup ditty grew, the flexibility/usability increased and we were able to pack in alot of functionality, that i thought others could eventually benefit from.  I wrapped up the function with some baselining capabilities, logging the start/end times of each backup with a secondary table, which provides two things.  First, helping us verify a backup runs every night and second, helping use see the backup size and file growth over time enabling us to do better storage/archival projections.  Lastly, it was important to me to make this entire process portable.  Deployment is as easy populating the tables with the DBs you want to backup and then schedule the SP to be run at some nightly interval.  thats it!

Now, before I share the code (hopefully it will be worth the wait),  here are a few things to consider when desinging your backup strategy:

1. Understand backup types (simple vs. bulk, vs. full)
2. Know where your backups are (dont want to go searching in time of need)
3. Encrypt/compress/password protect your backups (as the standard .bak file can be read with notepad!)
4. DB backups dont contain everything (also need master/userDB,msdb)
5. Take care of system DBs (master, msdb)
6. Plan for physical vs. logical failures (nightly backups good enough?  every 5 min/log shipping required?
7. Ensure recoveryability of a backup (media, network, data – run restore verify only & also test entire recovery model)
8. Forecast backup trends (do capacity planning and create a formal db recovery plan)
9. Consider use of direct-attached or online (NAS) volumes for backup – tapes are no longer the standard for backups!
10. Keep Control – if you are the DBA, dont rely on others with missing documention to jeapordize your restore.

Initial questions to ask.  How much data can be lost? Are there any SLAs you have to support?  What data security considerations are required?  Did you remember to backup indexes?  Some best practices include, running DBCC, then nightly backup with incremental log-backups periodically throughout the day as the business requires.

Hopefully that help to get you into the backup mindset.  Lots of things to consider, and theres certainly no one size-fits-all.  I’ll be posting the source code to my sql scripts in an upcoming post – which I hope can help you save time modernizing your backup efforts!?

SQL Tuning w/Kevin Kline

This morning I attended a breakfast seminar hosted by Quest Software featuring Microsoft SQL Sever MVP, Kevin Kline at the Troy Marriott (Michigan). 

In my role, I have to stay abreast of both leading and/or emerging technologies, but also current best practices; everything from software development methodologies to infrastructure maintenance/optimizations to keep our systems performing and most importantly, directing our staff when they aren’t.  Sure, its easy to argue that nobody in tech today can master all the increasing complex technologies within IT, however, when you’re in a small (but growing) company like ours you’re forced to be resourceful.  To get by, I do alot of reading and take opportunities to participate in as many industry outlets as possible.  If I can’t draw from my own experience to solve a problem, I’ve atleast been exposed to enough channels (products, companies, individuals, etc) to find the experts that can – Kevin Kline is one of those experts.

While I’ve never had to call upon Kevin for help, I have followed his work over the past couple years, both through Quest and a few of the books he’s written and magazines he writes for.  It was nice to finally meet him and validate some internal tuning techniques we’ve been utilizing compared to those used by himself, or companies he’s worked with.  The session ran about 3 hours and was divided primarily into two sections, managing data avalanches (Very Large DataBases or VLDBs) and SQL Tuning.  Some highlights of the session are below:

• What is a VLDB?
• • In years past it may have been about size, for example:
    • 1980 = 100mb
    • 1990 = 1gb
    • 2000 = 1tb
    • 2005+ = 10tb
  • Today however, its more about what Kevin calls, “Is it easy?” meaning, is it easy to manage? VLDBs are by default, complex, lots of data, lots of moving parts.
• Kevin then went on describing how & why were are storing more data today, many of which we’re well aware of:
  • SOX
  • online archives/backups/retention
  • more sophistacted business processes
• Regarding VLDBs, its equally important to understand disk I/O as much as it is about transactional throughput/sec or DB size.  Know your maximum burst reads/sec & trans/sec requirements per SLA before sizing your DB.
• Ensure proper block size (throughout his experience, 64k chunk size blocks are best)
• He, later went on to describe, most DBAs spend a LOT of time fixing bad code of developers!  Gartner published a study showcasing where sql performance issues typically were recognized, irregardless of DB flavor:
  • 80% poorly writen tSQL
  • 15% poor architecture/design
  • 5% hardware
• Progressive organizations are even moving away from tape all together, since disks have become so cheap, some companies are deploying scenerios such as:
  • Local (real-time backups) to a RAID 10 SAN
  • Short-term archive to RAID 5 online NAS devices
  • Longer-term archive to cheap SATA devices
• Place tables and/or indexes in seperate file groups on seperate drives
• Ideally, run backups serially vs. in parallel 
• Of course, pre-size, dont rely on autogrow
• Add spindles for read, controllers for write
• Partioning for highly stressed databases

In closing, Monitor -> Tune -> Repeat.  A link to his slide deck will be available shortly.