SqlAdvice

Determine SQL Server Version

gstark — Wed, 13 Feb 2008 13:43:18 GMT

If you need to know the version you are currently running of SQL Server you can easily get it by running the following query.

 SELECT 'SQL Server ' + 
 CONVERT(varchar(100),SERVERPROPERTY('productversion')) + ' - ' +
 CONVERT(varchar(100),SERVERPROPERTY('productlevel')) + ' - ' +
 CONVERT(varchar(100),SERVERPROPERTY('edition'))

Arithabort Option Affects Stored Procedure Performance

gstark — Tue, 12 Feb 2008 21:34:50 GMT

I have noticed that at times I will have a stored procedure start to take an unusual amount of time to complete. In trying to debug this I grab the SQL that is being called from the application and paste it into SQL Server Management Studio only to have it come back in a second. So I try again from the application, and it takes well over a minute. After hunting around and pulling out my hair as to why this would be I discovered that the connection from the application has the Arithabort option off and in SQL Server Management Studio, by default the Arithabort option is on. So I went ahead and ran a set Arithabort off and then ran the stored procedure within SQL Server Management Studio and sure enough it ran forever. From what I can tell having this option off was causing the query optimizer to pick some horrible query plan. I know SQL Server caches query plans by the connection options, but this just doesn't make much sense to me. The best fix I have come up with for this is to compile the proc with the "with recompile" option. I don't really like this option as SQL Server won't cache the query plan, but I haven't found any other solutions that work consistently. You can do this as such.

Create Procedure MyStoredProcedure
  @MyParameter varchar(10)
with Recompile
as
   select 1

I am interested in what others have found for a solution to this problem or any other insight into this as it is quite annoying and if you haven't run into it before it can be quite time consuming and annoying.

Off Topic: Strange VMWare Contest Rules

gstark — Thu, 10 Jan 2008 20:13:25 GMT

This has to go down as one of the strangest requirements for a contest ever. Check out the official rules for VMWare's contest to win an iPhone. In section 4 "Selection of Winner" it states

"Canadian residents, if selected as a winner, must correctly answer a mathematical skill-testing question in order to receive a prize."

Apparently VMWare wants to make sure they are giving the iPhone to "educated" Canadians.

Here is a link to the actual pdf on their site for those that want to see it with their own eyes.

http://campaign.vmware.com/docs/Legal_Copy_1939.pdf

I have to believe that this was a joke and someone forgot to remove it...regardless it is definitely odd and very funny.

SQL Server 2008 November CTP Released

gstark — Mon, 19 Nov 2007 17:45:46 GMT

Microsoft has released the latest SQL Server 2008 CTP. This release has tons of new features including Intellisense for SSMS. This one also includes the spatial datatypes which I know a lot of people have been waiting for. You can go get the newest CTP here. For a list of all new features included in this CTP, you can go here.

Increase Animation Speed in SQL Server Management Studio

gstark — Fri, 16 Nov 2007 14:34:51 GMT

I set all my tool bars to be fly outs and it is fairly annoying how slow the animation is. I found this link and decided to try it out and it works great!

http://blogs.msdn.com/euanga/archive/2007/10/01/ssms-window-animation-speed.aspx

I would be interested in knowing other configuration settings you can change in that file as well. Let me know if you know of any others.

Full Text Indexing Overhead

michael.k.campbell — Fri, 09 Nov 2007 23:26:33 GMT

I think that there's a tendency among DBAs and SQL Server wonks to be a bit dismissive of Full Text Indexing. I think that in a lot of cases people tend to think of it in terms of adding significant overhead.

For a long time I actually looked at it as being a more powerful, but more EXPENSIVE, form of searching. Then I actually had the chance to work with it (in a past life) a good deal, and found that it didn't really add that much overhead in a lot of cases. In fact, I found that it was actually pretty cool technology and afforded a lot of functionality with no noticeable performance hits. Though, I found that indexes frequently crashed a lot and needed an extra bit of 'love' to keep them functioning correctly. (This was all on SQL Server 2000.)

But in some cases it can yield insane performance benefits
Fast-forward to today. I'm currently working with a client that has a decent sized database (about 20GB). More than 5GB of that DB is made up of a 'Books' table - which has about 8M rows. The table has both and author and title domain, and each of them is pretty wide (nvarchar(300) and above). This table provides the backing for key functionality, and users frequently search for authors or titles using LIKE and wildcards. We all know that's nasty - as it forces a scan.

These searches were actually causing a table-scan, which was throwing off excessive reads, and generating massive amounts of CPU utilization. To the point where you could literally SEE each of these queries show up in task manager - as the CPU spiked to handle the underlying scan. The image below helps highlight the severity of the problem - with each of the scanning queries represented by the spike you can see:

I threw an index against each of the columns, figuring that a search on something like '%JRR%Tolkien%' against such a big table would likely prefer to use an index scan instead of a table scan. Both of those indexes required 533MB of Disk to create. And, sure enough, SQL Server used those indexes as soon as they appeared, but it only cut the reads in about 1/4th of what they used to be, and still kept CPU utilization about where it had been before - meaning that you could still SEE these queries being executed by their tell-tale CPU spikes.

Enter Full Text Indexing
I figured that since Full Text Indexing actually tokenizes index data, instead of merely storing it in a B-Tree, that we'd end up with a much smaller index structure. I also hoped, that if we could get a smaller index to work from, we'd see a commensurate decrease in the amount of reads/churn going on on the server.

Creating a Full Text Index on 8M+ rows took a fair while. But I was happy to see that it was only 133MB (compared to the 533MB of the 'traditional' indexes). That made me hopeful that we'd at least see around a 1/4th reduction in the amount of reads.

Turns out I got way more than I had anticipated. The actual execution plan for the query using LIKE and % was coming in at a cost of around 27 or 28 on a consistent basis. It was also taking 5-7 seconds for each query. (Yeah, that puts it almost into the realm of making it a 'query from hell' - but definitely something you don't want people just firing off in an 'ad-hoc' fashion like they actually NEED to do with this query in question.)

With the full text index, the actual execution plan drops down to .27 - making it fully 100 times faster than its traditional counterpart. Execution time dropped down to 0 seconds (in Management Studio) as well. In past cases where I had used Full Text Indexing, I was only dealing with a table with less than 300K rows. Suffice it to say I was pretty tickled at the insane performance boost provided on an 8M row table with semi-wide domains (author and title) being indexed.

So, keep those insane performance benefits in mind the next time you're tempted to be a bit dismissive of the 'overhead' that Full Text Indexing might add to your environment.

Microsoft's direction with SQL Server Tools

michael.k.campbell — Fri, 09 Nov 2007 05:42:28 GMT

I'm with Stephen Wynkoop on this one - I'm not sure I'm too hip on Microsoft's emerging direction to divide SQL Server Tools into Developer and Admin Roles.

Stephen just posted an editorial based on an interview that he had with Matt Nunn, and points out that Matt confirmed that Microsoft is trying to divide tools along discipline lines - wherein developer related tools will be in Visual Studio (VS) and DBA related tools will be in Management Studio (MS).

Stephen does a good, non-inflamatory, review of the pros and cons - but effectively ends with the opinion that he doesn't think it works for mixed-hat DBA/Developers.

I think he's spot on. Most likely because I think that there are likely VERY few DBAs out there that don't see some part of their job related to writing, modifying, or tuning code. Even if it's just a question of creating spiffy queries based on dynamic management views, most DBAs are going to want full-powered development IDEs/tools at their disposal to quickly pound-out scripts that they'll likely end up saving and occasionally re-using.

I'd say give developers powerful tools as planned, give DBAs the additional 'DBA' related tooling, and then give DBAs the option to install additional tools and support when they install MS to their own boxes. Just make it an option. Otherwise I really see myself possibly switching to use a tool from a third party.

Change Login's Default Database in SQL Server

gstark — Tue, 16 Oct 2007 12:18:33 GMT

If you have ever dropped a database and found yourself not able to login because your default database no longer exists you know it can be quite annoying. You can still connect through query analyzer by connecting to a different database, but you won't be able to do anything through the object explorer as it always defaults to your default database. The sql below will solve your problems.

alter login  MyLogin
with default_database = master

If you need to alter a login that is using windows authentication use the following code. The only difference is you just have to specify the domain and the directory login and put it in brackets.

alter login  [MyDomain\MyLogin]
with default_database = master

I hope this helps and if not at least I will be able to easily find the solution next time I run into it.

Retrieve Index Details and Columns in Sql Server 2005

gstark — Wed, 10 Oct 2007 13:49:05 GMT

I frequently need to look at what indexes are on a table and I am really not a fan of using object explorer in SSMS as I like to stay in the query window. Typically if I want information about a table I simply highlight the table name in the query window and hit Alt-F1. If you aren't familiar with keyboard shortcuts in SSMS see my post here. While this does give you the indexes that are on the table and it will show you the equality and inequality columns on the index, it won't show you the included columns on the index. So I decided to dig into the Sql Server Dynamic Management Views (DMVs) and write a query to return all the information about an index or even all indexes on a table. Here is what I came up with. This will return all indexes for a table.


select object_name(i.object_id) as TableName, i.name as IndexName, c.name as ColumnName,
       i.Type_Desc, ic.Is_Included_Column, key_ordinal
  from sys.indexes i 
  join sys.Index_columns ic on ic.object_id = i.object_id and  ic.index_id = i.index_id
  join sys.columns c on c.object_id = ic.object_id and c.column_id = ic.column_id
  where object_name(i.object_id) = 'MyTable'
  order by i.type_desc, i.name, Is_Included_Column asc, ic.key_ordinal

Let me know if you have any questions or improvements.

Archive and Compress Data from SQL Server

gstark — Thu, 27 Sep 2007 12:11:43 GMT

I recently blogged about deleting off data from a table by looping through one day at a time. Strangely enough I had a similar need pop up a few days later although this time I wanted to keep the data around just in case I need it later. I really didn't want to put the data into another table so I decided it might be nice to dump the data to a file and then zip it up into a zip file per day. This way if I ever need the data again I can easily bcp the data back into the original table. On my server I actually

This actually was pretty straightforward to do. Basically we can just loop through the data by a given interval (in my case I chose a day), BCP the data to a file, zip the file, and finally delete the data. Here is what my code looks like. Once again I used a numbers table as in my previous post. One item to note is that you will need some executable to do the zipping of the file. In this case I just used zip.exe. Also if you are on 2005 you will need to enable xp_cmdshell as well.


declare @startdate datetime
declare @EndDate datetime
declare @period datetime
declare @QueueVendorOrderActivityID uniqueIdentifier 
declare @cmd varchar(1000)

set @startdate = '9/01/2007'
set @enddate = '9/15/2007'

declare datecursor cursor forward_only read_only
    for Select dateadd(d,Number,@startdate) from Numbers
          where Number <= datediff(d, @startdate, @enddate)
          order by Number
open datecursor

while (1=1)
begin
  fetch next from datecursor into @period
  if @@fetch_status <> 0
    break;
    -- build cmd string to execute for bcping out the data  
    set @cmd = 'bcp "select * from MyTable where createdate between ''' + 
        convert(varchar(50), @period, 112) + ''' and dateadd(d, 1, ''' + 
        convert(varchar(50), @period, 112) + ''')" queryout c:\MyTable' + 
        convert(varchar(50), convert(varchar(50), @period, 112), 112) + '.dat -Umyuser -Pmypass -c'
    
    exec xp_cmdshell @cmd
    -- Now zip the file that we created.
    set @cmd = 'c:\zip c:\MyTable' + convert(varchar(50), @period, 112) + '.zip c:\MyTable' + 
        convert(varchar(50), @period, 112) + '.dat'
    exec xp_cmdshell @cmd
    
    -- Delete the raw file
    set @cmd = 'del c:\MyTable' + convert(varchar(50), @period, 112) + '.dat'
    exec xp_cmdshell @cmd
    
    -- delete all the data that we just archived out from the table.
    delete from MyTable where createdate between @period and dateadd(d, 1, @period)
end

close datecursor
deallocate datecursor

So far this seems to be working fairly well. Let me know if you see any issues with this or have any other tips.

Enable XP_CMDSHELL in SQL Server 2005

gstark — Thu, 27 Sep 2007 11:54:36 GMT

I needed to use xp_cmdshell recently and discovered that it isn't on by default on SQL Server 2005. I had to dig a bit to find out how to enable it. The code is very simple. Figured I would blog it here so I can easily find it next time.

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'xp_cmdshell', '1'
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

Iterate Over a List of Dates using a Numbers table in SQL

gstark — Wed, 19 Sep 2007 13:40:30 GMT

Recently I was helping Steve archive off a bunch of data from one of his tables. Unfortunately he didn't have a ton of diskspace to use some of the techniques I normally would. Steve recently posted a solution using powershell to do this. I told him about an easy way to do this using a cursor and a numbers table and he told me to blog it. This is an excellent use of a numbers table which if you don't have one of these in your database, you should create one.

Here is a script to create a numbers table.

CREATE TABLE dbo.Numbers
(
    Number int IDENTITY(1, 1) PRIMARY KEY
)
GO

declare @MaxNumber int
set @MaxNumber = 65535
WHILE 1 = 1
BEGIN
    INSERT INTO dbo.Numbers DEFAULT VALUES
    
    IF scope_identity() = @MaxNumber 
    BEGIN
        BREAK
    END
END
GO

So basically Steve wanted to delete all data out of a table one day at a time for a date range and then shrink down the log to avoid . The easiest way I could think of to do this is to create a cursor of all the dates in that date range and then loop through them. Notice the join to the number table for all Numbers between 0 and the datediff of the start and end date. Here is the code...


declare @startdate datetime
declare @enddate datetime
-- Declare the dates 
set @startdate = '01/01/2007'
set @enddate = '10/01/2007'

declare @period datetime

declare datecursor cursor forward_only 
    for Select dateadd(d,Number,@startdate) from Numbers
          where Number <= datediff(d, @startdate, @enddate)
          order by Number
open datecursor

while (1=1)
begin
  fetch next from datecursor into @period
  if @@fetch_status <> 0
    break;
  DELETE stats WHERE period = @Period

  BACKUP LOG stats WITH NO_LOG
  DBCC SHRINKFILE (stats_log, 2)        

end
close datecursor
deallocate datecursor

Here is a link to a good post on other uses of a numbers table. As always feel free to comment on any improvements or ask any questions.

Remove Extra Spaces when Pasting from Outlook to Sql Server Management Studio

gstark — Fri, 14 Sep 2007 14:01:01 GMT

I seem to have this problem that when I paste sql code from my email into Sql Server Management Studio that it doubles all the line breaks and I have to go and remove them all. Most of the time this isn't a big deal because the Sql is only a couple lines. However if you get a ton of Sql emailed to you it can be quite annoying. I finally found a way to quickly get rid of them. Simply bring up the find and replace window and change the find to use Regular Expressions. Then do a find on \n\n and replace that with \n.

If anyone knows of a way to change a setting in Outlook to avoid this please let me know.

Retrieve File Contents using SQL Server 2005 SQL CLR

gstark — Thu, 06 Sep 2007 18:25:59 GMT

I recently had a need to load a bunch of pdf files into my database. The problem was that the directory the files were in contained a bunch of other pdf files that I didn't want or that were no longer needed so I couldn't just upload every PDF in the directory. I did have a database table that contained all the file names that were still valid. I was wishing for a way to simply join from that table to the file system and load all those files. I certainly could have just written a quick exe that did this by retrieving the list of file names from the database and then grabbing the file and uploading back into the database. Instead I decided to write a CLR table value function that took in a directory path and a file search criteria and returned a table with the filename and the binary of the file. This way I could simply do an insert into my table all from t-sql. This ended up being a lot easier than what I thought and it worked quite well. Below is the code for the CLR function. Simply compile this code and then load it into your database. This will create a function called FileReader. You simply pass in a directory path and a search criteria (i.e. *.pdf).

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Collections;

public partial class FileReader
{
  [SqlFunction(FillRowMethodName = "FillRow", 
              TableDefinition = "FileName nvarchar(200),FileContent varbinary(max)", 
              Name = "FileReader")]
  public static IEnumerable InitMethod(String Directory, String FileCriteria)
  {

    DirectoryInfo d = new DirectoryInfo(Directory);
    return d.GetFiles(FileCriteria);
  }
  public static void FillRow(Object obj, out SqlString FileName, out SqlBytes FileContent)
  {
    FileInfo fi = (FileInfo)obj;
    FileContent = new SqlBytes(File.ReadAllBytes(fi.FullName));
    FileName = fi.Name; 
  }
}

Here is an example of how to use the function.

select * from dbo.FileReader('d:\', '*.txt')

and what the result was....

FileName             FileContent
-------------------- ----------------------------------------------------
Test.txt             0x6173646673646661736466...
Test2.txt           0x61736466736466617364660D0A0D0A6173626C61736661666C...

Here is some example t-sql to show how I used this to insert the files into a table.

Create table _FilesToGrab (FileName varchar(50))
Create table _Files (FileName varchar(50), FileContent varbinary(max))

Insert into _FilesToGrab VALUES ('test.txt')

Insert Into _Files (FileName, FileContent)
select f.FileName, fc.FileContent
  from _FilesToGrab f
  Cross Apply dbo.FileReader('d:\', f.FileName) fc

select * from _Files 

Drop Table _FilesToGrab   
Drop Table _Files

So my final solution consisted of this function and then one insert into statement with a select.

Hopefully this function comes in handy for someone else. If any has any suggestions on how to improve on it please let me know and I will see what I can do.

Move TempDB Sql Server 2005

gstark — Mon, 20 Aug 2007 21:19:14 GMT

I recently needed to move the TempDB on my Sql Server so I looked in Books Online and my initial thought based on what it said was that there is no way that will work. Basically it says to find where the files are, and then move them to the new location followed by an alter statement to point the database at the new location. Well of course this didn't work because the tempdb files are in use. After thinking about it I just decided to try to alter the master database and point it at where I wanted the tempdb files to be and then just restart since Sql Server creates the tempdb again when you restart it. Sure enough it worked. Here are the steps I took.

1. Find out where the TempDB files are (not that you really need to know for anything since you can't move them without stopping SQL Server).

SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

2. Alter the master database and point the tempdb to the new location.

USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\SqlServer\Data\tempdb.mdf');
GO
ALTER DATABASE  tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'D:\SqlServer\Data\templog.ldf');
GO

3. Restart SQL Server

Upon restarting you will see new TempDB files created. Alternatively I suppose you could do steps, 1 and 2 above and then stop SQL Server, move the TempDB files to the new location, and then start SQL Server. I suppose that would be slightly better since then you wouldn't have to wait for the TempDB files to auto-grow which can be a performance hit. Either way if you try to follow books online you won't have any luck.

Recover Suspect SQL Server Database

gstark — Tue, 07 Aug 2007 14:37:57 GMT

Where I work we have about 160 instances of SQL Server Express running on laptops and as you can imagine these database instances tend to become corrupted from time to time. One of the most frequent issues is that a database becomes suspect. Obviously your first choice should be to restore a backup and the transaction logs since the last backup. However if you don't have this (as I never do on my SQL Server Express instances) this is usually pretty simple to take care of by putting the database into single user mode and then emergency mode and then running a dbcc checkdb. Here is the T-SQL you should use. NOTE: You should be in the master database when you run this.

1) Alter database mydb set Single_User

2) Alter database mydb set Emergency

3) DBCC CheckDB ('mydb') -- This will tell you the Repair level to use

4) DBCC CheckDB ('mydb', { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD })

5) Alter database mydb set Multi_User

After a successful DBCC CheckDB the database is back in a consistent state and it's status is now online. For more information on DBCC CheckDB go here.

I normally run the REPAIR_ALLOW_DATA_LOSS as the users running the express instance generally don't have much critical data so if we lose some data it isn't that much of an issue. NOTE: Running DBCC CheckDB can take a long time to run and you may want to consider running it with the "noindex" option.

Let me know if you have any other strategies for recovering databases in suspect mode.