Commentary and tips and tricks for the Microsoft SQL Server product
So, you are considering upgrading to SQL Server 200x from a previous version. What are some of the key issues that you need to consider, or at least be made aware of, prior to upgrading?
Fortunately the heavy lifting of answering that question has already been done for you. It's available to you in the form of an Upgrade Technical Reference Guide. There's one available for upgrading to SQL Server 2008 and one for SQL Server 2005.
SQL Server 2008 Upgrade Technical Reference Guide
SQL Server 2005 Upgrade Technical Reference Guide
Of course, you'll...
Early this morning, Bob Ward of Microsoft PSS SQL Support fame authored a post on the PSS Support blog entitled "SQL Server Support in a Hardware Virtualization Environment". As always, Bob's comments are helpful. In the post, he clarifies and elaborates on Microsoft's official position on support for a SQL Server system in a Virtualized environment.
I normally just twitter interesting links such as this, however given all of the questions and uncertainty of virtualization recently, I thought this on warranted its own blog post, too.
Here's the link - http://blogs.msdn.com/psssql/archive/2008/10/08/sql-server-support-in-a-hardware-virtualization-environment.aspx
My latest Simple-Talk article is live. It's entitled Using Covering Indexes to Improve Query Performance.
Let me know what you think and if you'd like to see more articles/postings on similar topics.
Late last year, I posted a blog that described the undocumented sp_MSforeachtable stored procedure in Microsoft SQL Server and how it could be used to execute commands on each table within a database. The examples I gave were executing DBCC CHECKTABLE and EXECUTE sp_spaceused() for each table in a given database.
But there's another undocumented stored procedure designed for iteration. This one allows us to iterate through each database in a SQL Server instance. It's called sp_MSforeachdb.
It's usage is very similar to that of sp_MSforeachtable; simply execute the stored procedure, passing it the command that you'd like to execute as...
Fellow SQL Server enthusiast and online friend of mine, Jonathan Kehayias is very active in the MSDN Forums. He frequently provides good, insightful information to those seeking advice.
In response to a recent question in the Database Engine Forum, Jonathan provided a good example how a process marked as sleeping can still hold locks on a SQL Server resource. I'd like to share a slightly modified version of it with you; you can read his original postings here.
Let's consider a very simple table in tempdb. Use SQL Server Management Studio to execute the following query:
USE tempdb ;
--create a table...
Next week at the Nashville SQL Server User Group, John Rives of Amniox is going to speak about using SQL Server with clustered virtualization. Unfortunately, I'm not going to be able to attend due to a prior commitment, however I have spoken with John about his presentation and it definitely sounds promising. If you're in the area, I'd encourage you to come on out. I certainly wish I could be there.
I don't have any SQL Servers in a virtualized environment. In fact, I have only worked with one company that does, and that is on a very underutilized server with...
As Geoff and Derek have already posted, SQL Server 2008 was released to manufacturing (RTM'd) yesterday.
Eager to get the bits? They're available from the MSDN download site.
Thanks to everyone who attended my SSRS class in Charlotte this week. As promised, here is a list of online resources that may prove useful to you as you work with Reporting Services.
Reporting Services Tutorials
Reporting Services Samples on Codeplex
Reporting Services Developer Info Center
Beginning SQL Server 2005 Reporting Services Simple-Talk article series by Steve Joubert
Microsoft MSDN Reporting Services Forum
TechNet SQL Server TechCenter
Additionally, the following...
I was searching for something online the other day and stumbled upon this TechNet article - Working with tempdb in SQL Server 2005. It's a good little article and I wanted to share it with you.
I was recently perusing the the MSDN SQL Server forums and noticed a question about localization of Microsoft SQL Server messages. I replied to the post with a mention of the sys.messages table - the table where SQL Server messages are stored. I blogged about this system table a few months ago.
Afterward I started playing around some more with the sys.messages table. I started with the following basic query.
The first thing to notice is that each message_id has one or more languages associated with it. Message_id 21, for example, has a row for language_id 1033, 1031, 1036,...
Thanks to everyone who attended my "Identifying Performance Bottlenecks" session this morning at DevTeach/SQLTeach. As promised, here is the PowerPoint slide deck I used during the presentation.
Regarding the question of on the performance of LINQ, Bob Beauchemin recently posted a series of blogs entries on the topic. Here's a link.
And here's a link to series of posts on the Dynamic Management Views (DMVs) that Louis Davidson authored.
The nomenclature used for referencing software releases and updates can be quite confusing. Let's consider an example to illustrate what the various terms mean.
As a new version of a product is being developed, it may be made available to select customers and community members for early testing. This is sometimes called alpha builds of the product.
As development progresses and the product becomes more and more polished, it's provided to a wider audience. This used to be called beta releases; for example beta 1, beta 2, etc. However a few years ago Microsoft changed the terminology for SQL Server pre-releases. They...
I hope that everyone that works with Microsoft SQL Server is aware of the MSDN SQL Server Forums. If not, you should really go check them out. They're a great resource when you're staring at a tough SQL Server-related problem.
Now, some of the Moderators, Answerers, and MVPs have gotten together to create a new resource to supplement the Forums; it's called SQLExamples. We just started it a few weeks ago so it's still very much in its infancy and somewhat sparsely populated. But it's growing quickly. I think it'll be a boon for SQL Server professionals the world over...
At the 2007 PASS Community Summit in Denver, a keynote speaker made a passing comment about how there has not been a security bulletin released for SQL Server in over three years! I forget which speaker made the statement, but I found it utterly amazing. Not a single security bulletin released in over three years! Could this be true?
If you've worked with SQL Server for a while, you'll undoubtedly remember SQL Slammer, the worm that hit thousands of SQL Servers around the world in 2003. It's effects were nothing short of devastating for many companies.
I made a mental...
Thanks to all 60+ people who braved the flood warnings in Nashville to attend the SQL Server User Group meeting earlier today. I hope you found it to be worth your while. Some of your questions and comments at the end gave some some great ideas for future presentations. Thanks!
And thanks to Quest and RHT for sponsoring our meeting meeting today. If your in the Nashville areas, I'd encourage you to come on out for our next meeting, most likely sometime in May.
As promised, here's a link to the presentation materials, both the PowerPoint presentation and the demonstration code.
If you've used SQL Server for a while, you're probably familiar with the @@version function. Selecting the function in a query window produces the following results on my system.
From this, you can quickly determine that I'm running SQL Server 2005 Developer Edition version 9.00.3054.00 on an Intel X86 processor. It may also appear that I've installed SQL Server SP2, however that is rather misleading. The "Service Pack 2" text in the above image refers to the operating system, not the SQL Server system.
To determine SQL Server's service pack level, use the built-in SERVERPROPERTY function with appropriate parameters -...
Interested in learning more about Microsoft SQL 2008 Server Reporting Services? I recently stumbled upon a White Paper on the Microsoft site that provides a good overview. While weighing in at only 14 pages including the title page, table of contents, and copyright stuff, it does provide a good, abeit somewhat general, overview of the product and it's capabilities.
You can download it from here.
I recently worked with Buck Woody to turn one of my blog posts into a podcast. It's my first podcast! :) I recorded it about midnight one evening after everyone else in my family had retired for the evening. With the house so quiet, I didn't realize I was whispering until I heard it mixed with Buck's projecting and charismatic dialog. Here's a link to the podcast. I'd love to hear what you think and if you'd like to hear more of these.
If you're not familiar with Buck Woody's work in the SQL Server community, I would encourage you...
I just completed teaching a Microsoft SQL Server Reporting Services class in Charlotte, North Carolina - a wonderful and beautiful place to visit!
In a couple of the demonstrations, we used some predefined formatting strings to customize the way a user can see a value in a report. For example, we used C0 to display a currency value with no numbers to the right of a decimal point.
One question that almost always comes up during the class is: where can I find a list of those predefined formatting strings?
Microsoft has them on the MSDN Library site, but the...
Last week, I completed an upgrade of a SQL Server 2000 server to SQL Server 2005. Not really a big deal; servers are routinely upgraded to a newer version of software. Sometimes it seems the upgrades come too quickly. Other times it seems like we are waiting for the proverbial pot of water to boil while eagerly looking forward the next release.
This server, however, included a Notification Services instance. It got me to thinking about upgrades and some of the changes in SSNS between v2.0 and 2005. I've compiled a "Top 10" list of enhancements to SSNS 2005. (I...
A very astute SQL Server professional and reader of this blog posed the following question in a comment to a prior posting entitled "Does the order of criteria the WHERE clause matter?" The comment, submitted through Plaxo Pulse, is not available outside the Pulse community so I've included it below since it's the genesis of today's post.
"What if you were using join statements? Does it matter what order your columns are used in the ON statement? ~ Ariel M."
There are several derivatives of the original question:
Does the order of tables referenced in the ON clause of...
Recently, I demonstrated that the order of columns in a composite index greatly determines its usefulness. Microsoft SQL Server can efficiently resolve queries using a composite index if the search criteria includes the first column in the index. If the search criteria omits the first column and includes only subsequent columns, the index is of no use to SQL Server for resolving that query. To see the proof, refer to this post.
The example I used in the prior post included the following query as a basis for the discussion.
After blogging yesterday that you can try the latest Microsoft SQL Server 2008 CTP online for free on the SqlServerBeta site, Microsoft has announced the release of a new CTP.
The February CTP available! And it's feature complete. If you have the bandwidth, head on over to the download site and get it while it's hot.
Note: at the time of this posting, the download page indicates that it was last updated in November, but it will take you to the February CTP download.
Had a chance to play with Microsoft SQL Server 2008 yet? If so, you've probably downloaded the most recent Community Technology Preview (CTP). It's not small either. The DVD's can take almost an hour to download at T1 speeds. And the virtual machine version is over 2 gigabytes!
For many, that's not an issue. But for the rest of us, downloads of that magnitude are a bit overwhelming. So what can we do?
Well, three organizations (Dell, MaximumASP, and PASS) have gotten together to create an online, virtualized way for us to test-drive the next version of the RDMS. You can...
The old adage "An ounce of prevention is worth a pound of cure" can be applied to many topics and areas of life. And while it has its origins in personal health care, nowhere is it more true than in an IT shop. Whether you're talking about high availability solutions or starting with a good database design, planning ahead is well worth the effort. Anyone who has been through an IT crisis can testify to that!
But alas, we live in a dynamic world and we can only make calculated guesses at what the the ounce of prevention should be....
A single column index is fairly straightforward to understand. You may have heard it compared to the index in the back of a technical book. When you want to find some information in the book, say DBCC INPUTBUFFER, you turn to the index in the back of the book and look up DBCC INPUTBUFFER. The index doesn't actually contain the information on DBCC INPUTBUFFER, it has a pointer to the page in the book where the command is described. So, what do you do? You turn to that page and read all about how DBCC INPUTBUFFER may be used. This...
That's right! After a bit of a break, the Nashville SQL Server User Group is getting back together again. Our first meeting back will be this Friday, February 15th at 11:30am. Our own Kevin Kline is presenting a rather timely topic - "SQL Server 2008, Worth the Wait".
Check out our new web site for further details, http://nashville.sqlpass.org.
If you're in the Nashville area, come on by for a free lunch with friends from the SQL Server community.
To prepare for an upcoming technical presentation, I need to create some test data to use in a demo. I'm planning to show how different T-SQL programming techniques directly affect SQL Server's efficiency in resolving the query. So, 10 - 20 million rows of data will likely suffice for this demo.
The question is: how to create all of that data? Obviously, a simple INSERT...VALUES statement is good for creating up to a few dozen rows of data, but 10 - 20 million rows of data is out of the question for such a simple technique.
Fortunately, SQL Server has...
One the more welcomed enhancements of SQL Server 2005 Notification Services was the new Microsoft.SqlServer.Management.Nmo namespace. The namespace provides classes that may be used develop and administer SSNS instances and applications.
For example, the following C# console application may be used to iterate through each SSNS instance of a SQL Server 2005 instance, printing its name to the console window. If you'd like to try this code for yourself, don't forget to add a reference to the microsoft.sqlserver.smo.dll in your Visual Studio 2005 project.
In a prior posting, I demonstrated how the undocumented sp_MSforeachtable stored procedure can be used to iterate through each of the tables within a database.
In a separate posting earlier this year, I also demonstrated how the DBCC CLEANTABLE command can be employed to reclaim space that was once consumed by variable length character columns that have been subsequently dropped.
In this post, I'd like to combine the two topics and show how the sp_MSforeachtable stored procedure command may be used in conjunction with the DBCC CLEANTABLE command to reclaim space from all tables after one or more columns were...
In my initial post here on the SQLTeam site, I mentioned that for the prior three years I had blogged on another site. That blog was almost exclusively dedicated to SQL Server Notification Services.
If you are a regular reader of this new blog, you've undoubtedly noticed that I've broadened the scope to include topics of interest to DBAs, database developers, and general technologists.
As time permits, I'm still attempting to recover the SSNS tutorials and commentary from my prior blog. I'll post what I can recover as it becomes available.
In the meantime, here are a few links to...
In a recent post, I demonstrated how SQL Server will reuse space made available when rows are deleted from a table. This occurs automatically without any outside intervention on our part.
However under certain circumstances, SQL Server does not automatically reclaim space once used. If a table definition is altered to drop one or more variable length columns, the space consumed by those columns is not immediately made available for reuse by SQL Server. But that's to say that the space is forever lost. We can employee a DBCC utility reclaim the newly-freed space.
To illustrate this behavior...
A question that occasionally comes up from people who have made the switch to Microsoft SQL Server from Microsoft Access is "If I delete a bunch of rows, do I need to compact my SQL Server database?"
It's been many years since I've used Access, but I still remember the reason for their concern. Microsoft Access would continually add rows to the end of the table. If some, or even all, of the rows were deleted from the table, Microsoft Access wouldn't reuse the space. It just kept adding rows to what was the very end of the table and...
Even though SQL Server 2008 does not include the Notification Services (SSNS) component, it seems that companies still consider SSNS a very viable option for upcoming notification projects. I'm planning to write a blog or SQLTeam article in the near future to address some questions I'm frequently asked.
In the meantime, if you have decided to create a new notification application using SSNS, I'm reposting a short tutorial I created a couple of years ago. It's been modified and updated slightly.
In this blog we'll go through the simple steps that can be used to create a new SQL...
If you've worked with Microsoft SQL Server in a production environment for any length of time, you've undoubtedly been exposed to a number of different error messages. For example, many of us are probably familiar, too familiar perhaps, with error 1205 - the dreaded "you've been chosen as the victim of a deadlock" message.
Or maybe you are more of a developer than a DBA. So you may be more familiar with error messages like 2714 - "there is already an object named this in the database."
There may be many messages that we feel that we know by heart....
Year in and year out, the week of the PASS Community Summit is one of the highlights of my professional life. There are a lot of good things about the Summit, far too many for me to enumerate here. Maybe I'll consider that as a topic for a future post.
However, too much of a good thing can be bad, or at least inconvenient. As a member of the PASS Board of Directors, I am typically so busy at the Summit that I have little time to actually sit down and enjoy a presentation that someone else has worked so tirelessly...
A recent ZDNet article highlighted an event where two United Kingdom officials demonstrated just how vulnerable a new, but not updated, Microsoft Windows system can be.
It took one official from the Serious Organized Crime Agency a mere 11 minutes to discover the target computer on a wireless network, crack into it using open source tools that are commonly available on the Internet, and steal password files.
Why should a database professional care about such demonstrations, especially when its stated purpose was to edify consumers and small businesses on security practices?
Well, consider this. Many organizations have...
One of the things I really like about teaching technical classes is getting to know and talk with students, learning of the challenges they face and how they've addressed them. Frequently, I can learn from their experiences, too. So, it works out well for both student and instructor.
In a custom Visual Studio/SQL Server class last week, I made a passing comment about Visual Studio being a very good text editor, but that it lacked one feature that I really like in other editors - block copy and paste. I lamented that the Visual Studio editor doesn't allow you to select a rectangular...
As database administrators or developers, sometimes we need to perform an action on all of the tables within a database. For example, we may want to disable check constraints for all tables prior to loading a bunch of data; or perhaps we need to run DBCC CHECKTABLE for every table in our database to ensure the integrity of all pages and structures that make up the tables.
We could, of course, create a script that uses a cursor to loop through all of the tables in the database to do our bidding through dynamic SQL. Yuck!
Fortunately, there's a better way. It's the...
Thanks to all of you who attended the DevLink Technical Conference in Nashville, Tennessee! It was great to see such a turnout for the event. Big kudos goes to all those who carried the load and championed this event. Putting on an event like this is no minor task and is definitely a labor of love.
As promised in my session, here are the presentation materials I used - the slide deck and the demo scripts.
I hope you found it worth your while.
In a recent post, I demonstrated how the sys.procedures catalog view could be used to determine the date and time that a stored procedure was last modified in SQL Server 2005.
A similar technique can be used to determine the last time a user table was altered. The following query illustrates this technique.
SELECT [name] ,create_date ,modify_dateFROM sys.tables
The modify_date column is updated whenever a column is added or altered for the table. It's also updated if the clustered...
In SQL Server, much like other services with users, login accounts can be enabled or disabled. An enabled login can be authenticated and allowed access to database resources. A disabled login is not allowed to establish a connection to the SQL Server instance.
For example, let's create a login called JoeUser.
CREATE LOGIN JoeUser WITH PASSWORD = 'P@$$w0rd!' ,DEFAULT_DATABASE = AdventureWorks
USE AdventureWorks;CREATE USER JoeUser
In the first statement, the JoeUser login was created with a fairly complex password and a default database of AdventureWorks. Afterward, we switch to the AdventureWorks database and...
Ever wondered if it is possible to programmatically determine when the SQL Server service for an instance was last started?
It would be nice to have a built-in function that explicitly reports the time of the last service start, but one doesn't exist.
But we can still estimate the time of the service start to within a few seconds, which should be good enough for anything less than the exact calculations required for most strict Service Level Agreement (SLA).
Since we know that tempdb is recreated each time the SQL Server service starts, we can look to the creation date of that system database to...
Have you ever been troubleshooting a SQLNS instance and noticed the status code column in some of the views and underlying tables? For example, the NSSMTPNotifications view has a column named DeliveryStatusCode with a values that range from 0 to 6. But what does that mean? What does a value of 6 actually tell you?
Fortunately, the SSNS database contains tables with status code descriptions. Try running the following query in your application database. Note: you'll need to replace dbo with the appropriate schema name.
SELECT * FROM dbo.NSNotificationDeliveryStatusCodes
Figure 1 shows the results. You can see that a value of 6 in...
Recently, a client asked that I grant database access for several of their new employees. The client uses SQL Server 2000 and has an Active Directory in place to manage domain users and resources. Windows Authentication is used almost exclusively in this environment.
As I dutifully created the new Logins, granted access to the required databases, and assigned the users to the appropriate database roles, everything went as expected - that is, until I got to the last two employees on the list. As I attempted to create a new Login for those two employees, I received the following...
When SQL Server receives a new query, it attempts determine the best possible plan for resolving that query. It considers a number of different factors as it analyzes the query and maps out a way in which to retrieve the information requested.
Whether or not the query optimizer deems an index to be useful in resolving a query largely depends on the information contained in the statistics for that index.
If the statistics are outdated and do not accurately represent the distribution of values within the table, the query optimizer may not produce an optimal plan for resolving the query. Misleading statistics may result in...
As a consultant, I regularly need to determine when a stored procedure was last altered. Without having implemented a series of DDL triggers, how can this be accomplished?
In Microsoft SQL Server, you can easily retrieve this information from the sys.procedures catalog view. The following query demonstrates this.
SELECT [name] ,modify_date ,create_date ,*FROM sys.procedures
Of course you can take it a step further by limiting the results to a period of time where you know that no changes should have been made. For example, the following...
As database professionals, we may responsible for dozens, if not scores, of SQL Servers throughout our department or enterprise. Now that Microsoft has announced that Notification Services will not ship as part of the SQL Server 2008 product, how can you readily identify which of the servers in your charge have SSNS instances installed?
Fortunately for us it's rather easy. SSNS 2005 registers each installed instance in the msdb system database. The following query returns a list of every SSNS instance for the SQL Server instance.
To retrieve a list of all SSNS applications for the...
For the past three years of so, I've been blogging about my experiences with Microsoft SQL Server and the lessons I've learned along the way. During that time, I posted some pretty good tutorials, if I do say so myself. In particular, I regularly commented on an often underutilized facet of SQL Server called Notification Services.
For those not very familiar with the topic, Notification Services was initially released as add-on to SQL Server 2000. It's a highly scalable development framework and hosting platform for notification applications. It's based on technologies that most of us already know and love -...