If you’re well-versed in using PerfMon, I’d like to hear about the counters that you use regularly and what constitutes a good or bad value. 

However, it’s a rare individual who knows their way around PerfMon and its multitude of performance objects and counters.  Which ones should you track?  And even if you know the right ones to track, what values indicate good or bad performance? 

If you’ve ever struggled with knowing which PerfMon counters to track for SQL Server performance, take a look at my TechTarget series of screencasts at SearchSQLServer.com.  They’ll give you most everything you need to know about using PerfMon and assessing PerfMon counters to learn about the behavior of SQL Server.  It’s a four-part series with the first two sessions, PerfMon Counters for Tracking Memory and PerfMon Counters for the Windows OS, already posted.  These screencasts not only tell you which counters to track and why, but also what are the general rules of thumb for good performance when using these counters.

Enjoy!

-Kevin

If you’re well-versed in using PerfMon, I’d like to hear about the counters that you use regularly and what constitutes a good or bad value. 

However, it’s a rare individual who knows their way around PerfMon and its multitude of performance objects and counters.  Which ones should you track?  And even if you know the right ones to track, what values indicate good or bad performance? 

If you’ve ever struggled with knowing which PerfMon counters to track for SQL Server performance, take a look at my TechTarget series of screencasts at SearchSQLServer.com.  They’ll give you most everything you need to know about using PerfMon and assessing PerfMon counters to learn about the behavior of SQL Server.  It’s a four-part series with the first two sessions, PerfMon Counters for Tracking Memory and PerfMon Counters for the Windows OS, already posted.  These screencasts not only tell you which counters to track and why, but also what are the general rules of thumb for good performance when using these counters.

Enjoy!

-Kevin

P.S. I cover a variety of PerfMon counters in these screencasts.  Many of them are discussed in an excellent document and in blog posts put together by Microsoft PSS.  Read their blog at http://blogs.msdn.com/psssql/default.aspx.

Technorati Tags: PerfMon, Tools

A while back, Quest Software started a series of webcasts called “The Pain of the Week”.  These webcasts are about a specific area of difficulty for SQL Server DBAs and developers, and how specific Quest tools can help.

In the next webcast, I and my colleague from Quest, Bryan Oliver, will present a methodology and show you how to code faster for faster code using Toad for SQL Server.

Toad for SQL Server is uniquely designed to make work easier and more efficient for anyone coding Transact-SQL scripts and routines. From its top-rated debugger and built-in Intellisense, to its powerful tools for comparing and synchronizing multiple instances of SQL Server, Toad delivers improved performance that will save database developers hundreds of hours per year. 

All of the details for the event, including registration, are here:

     http://info.quest.com/kkblog-pow-codefaster-toadexpert

If you’re interested in coding faster in SQL and Transact-SQL and are open to trying new tools, then please join me for this presentation on April 3rd, Thursday, at 10:00 am central time.

Thanks,

-Kevin

Technorati Tags: TOAD for SQL Server

If you’ve read SQL Server Magazine, then you know that I write a monthly column about free SQL Server tools called Tool Time.  I recently wrote about a set of automation scripts from Microsoft’s internal MIS group that automates all sorts of regular tasks they perform on their SQL Server’s.

Today, I’d like to share some more scripts brought to my attention by Ola Hallengren.  Ola wrote some great scripts for backup, integrity check and index optimization that add to what Microsoft had written. I think that it in some areas has some advantages over Microsoft’s solution.

Ola’s index optimization stored procedure rebuilds indexes online or offline, index reorganization, statistics update, index reorganization and statistics update or nothing based on fragmentation level and lob existence.  This solution was made about 6 months ago and it has been running in a large production environment since then.  You can see them at:

http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
http://blog.ola.hallengren.com/_attachments/3440068/Documentation.html

Ola has put a lot of time in this and would really appreciate your feedback.  You can see more of his work at http://ola.hallengren.com.

I’d love to hear about any other database automation scripts you may have written.  Be sure to let me know so I can share them with the community.

Enjoy!

-Kevin

Technorati Tags: Automation

I read a lot of blog posts - a LOT.  Sometimes it’s interesting to sit back and evaluate all of the reading that I’m doing to try to glean ways of improving my efficiency.  One thing that has enormously improved my efficiency is installing a Google plug-in RSS reader.  Now, all the blogs that I read come into one sidebar where I can easily keep track of what’s new. 

So here are some of the posts I’ve been reading in the last couple days primarily covering SQL Server and also my other interests to a lesser degree:

http://sqlcat.com/whitepapers/default.aspx - The SQL CAT (Customer Advisory Team) are simply awesome.

http://www.sqlskills.com/blogs/bobb/2008/03/04/FeatureSynergyInSQLServer2008.aspx - Bob Beauchemin’s blog is always a wonderful site for great new information.  This particular article talks about the interplay of PowerShell and SSMS, which I’m trying to get up to speed on.

http://blogs.msdn.com/dtjones/archive/2008/03/07/getting-started-with-powershell.aspx - Dan Jone’s nice entry on getting started with PowerShell.

http://sqlblogcasts.com/blogs/ssqanet/archive/2008/03/04/pre-order-the-kalen-delaney-sql-server-internals-course-on-dvd.aspx - Kalen has been an expert in SQL Server since before it was a Microsoft product.  This DVD is very much worth it.  It’s actually really cheap considering how much information it contains.

http://www.sqlskills.com/blogs/paul/2008/03/05/TechNetRadioInterviewWithPaulAndKimberlyOnSQLServer2008Part1.aspx - Kimberly Tripp was the first person I ever took a SQL Server class from.  Her content has always been top-tier and now that she and Paul are working together, it’s only gotten better.

http://blogs.msdn.com/psssql/archive/2008/03/05/how-it-works-sqliosim-checksums.aspx - SQLIOSim is very useful for stress testing, but notoriously hard to interpret the results. (I wrote about this free tool some months ago in my SQL Server Magazine column, Tool Time.)  This blog post from the PSS team helps you decifer checksum error messages that might appear in the SQLIOSim log.

http://blogs.msdn.com/benjones/archive/2008/03/05/store-your-data-in-the-cloud.aspx - Okay, so Microsoft has plans to allow you to host your SQL Server data in the cloud.  That means that small businesses (or small teams inside of big businesses) can easily pop up SQL Server without the infrastructure or management headaches.  I’m not sure how much traction this will get, but it shows that Microsoft now considers Google to be its biggest competitor IMO.

http://blogs.msdn.com/sqlperf/ - I was intrigued by a posting from the SQL Server Performance Engineering team that not only did SQL Server 2008 launch at February party for Windows 2008 and Visual Studio 2008, it launched with exceptionally good TPC-E numbers.  This surprised me because, afaik, there’s still a lot of debug code in the CTPs.  I know this because my own TPC-C test were disappointing.  So I was wondering what the secret sauce was so that they could post strong numbers with a straight face.  I haven’t yet read the TPC disclaimers, but rest assured that I will soon.

http://blogs.technet.com/wardpond/archive/2008/03/06/are-you-paying-to-read-this-post-you-don-t-have-to.aspx - I like to keep up with what Ward Pond is up to and, at the moment, he’s ticked off at a variety of websites that are aggregators for other people’s content, including his own.  These websites charge monthly fees, but often create very little of their own content, instead repackaging what other people have written.  This is what my Google RSS reader does for free.

https://blogs.msdn.com/sqlserverstorageengine/archive/2007/02/14/wow-lots-of-blogs-from-the-sql-product-team.aspx - I always go back to this web page from time to time to make sure that I’m checking in on all of the SQL Server team blogs.  Right now, I’m making sure that all of the blogs with RSS feeds are in my reader so that I don’t have to go out and pull down their latest postings manually.

http://statisticsio.com/ - This is a new (to me at least) blog from Jason Massie that I found to be fun and interesting. I think the SQL All Stars competition is what caught my eye.

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1296396,00.html?track=sy200&asrc=RSS_RSS-3_200 - I feel like I never seem to know enough about how the SQL language and XML interact on SQL Server.  This is a good article by Michelle Gutzait examining XML on SQL Server, compared to simply using VARCHAR(MAX).

http://benchmarkfactory.inside.quest.com/index.jspa - I use Benchmark Factory (from my employer, Quest Software) to do my large-scale SQL Server testing.  I was on the discussion forum reading some tips for doing a 3000 concurrent user TPC-C test.

And now for something completely different, I’m a big fan of the book Freakonomics and the accompanying blog hosted by the New York Times.  In this book (and on their blog), the authors look to interprete the data as it really is, rather than the data as we wish it meant.  The results are sometimes shocking and always interesting (social conservatives beware).  In this particular blog post (http://freakonomics.blogs.nytimes.com/2008/03/04/when-journalists-gripe/), they point to a website where journalists gripe about being a journalist.  I was interested in this because I think old world journalists today are what icemen were in the 1920’s, on the verge of extinction.  So I’m curious to see what’s happening in their world.

I’m also very concerned about the current state of the economy, which has been brewing for quite a long time now, and the dire straights we’re now in:

http://dailykos.com/storyonly/2008/3/7/63218/05713/729/471169
http://ap.google.com/article/ALeqM5iwL8pyStPKCF3LSCswXmM4jrhyygD8V8724G0
http://www.reuters.com/article/domesticNews/idUSN2862827920080229
http://www.fool.com/investing/value/2008/03/03/its-so-much-worse-than-you-think.aspx

I hope you find one or two of these posts of value, as I did.

Enjoy,

-Kev

Technorati Tags: tips

What you’re asking is partially covered in the Books Online topic entitled Rollbacks and Commits in Stored Procedures and Triggers - the article is worth reading if you haven’t looked at it before. Having said that, I’m not a huge fan of the advice provided because it’s rule-of-thumb-esque; it ignores the fact that in some cases business rules are business rules and “imperfect” code needs to be implemented. Theoretically you shouldn’t ROLLBACK within a trigger. So, just like “If it hurts when you do that, don’t do that” if you don’t want the client to see the transaction count message don’t rollback in the trigger. By the way, whatever you do, DO NOT open a nested transaction within the trigger just to make the error go away, i.e. don’t implement a worse practice to buffer the consequences of a not-the-best practice.

Anyway, so far we’re not having any fun…so let’s leverage a cool feature implemented in SQL Server 2005 to solve your problem. Using TRY…CATCH you can custom-tailor your error message like so:

/* Setup my lame-named table and trigger */
IF EXISTS(SELECT * FROM sysobjects where type='U' and name='MyTable')
DROP TABLE MyTable;
GO
CREATE TABLE MyTable([id] int,[desc] nvarchar(500));
GO
IF EXISTS(SELECT * FROM sysobjects where type=’T’ and name=’MyTableTrigger’)
DROP TRIGGER MyTableTrigger;
GO
CREATE TRIGGER MyTableTrigger ON MyTable AFTER INSERT
AS
BEGIN
IF (SELECT COUNT(*) FROM MyTable)=2 AND @@TRANCOUNT>0
BEGIN
ROLLBACK TRAN;
– RAISERROR(’This is my triggered error message’,16,1);
END
END
GO
/* Open a transaction */
BEGIN TRAN
BEGIN TRY
INSERT INTO MyTable SELECT TOP 2 [id],[name] FROM sysobjects;
END TRY
BEGIN CATCH
IF (SELECT ERROR_NUMBER()) IN(3609,3616)
RAISERROR(’This is my coded error message’,16,1);
ELSE
SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
IF @@TRANCOUNT>0
COMMIT TRAN;

Running that code in SQL Server will return your custom message (remove the comment in the CREATE TRIGGER section to display a different error). So, there are two ways to get what you’re looking for. Oh, and if you’re thinking, “that’s great, but the INSERT is being executed by code” just wrap the INSERT in a stored procedure and have your code call the procedure - you’ll be implementing a certifiable best practice and increasing the likelihood of query plan reuse at the same time.

Hope that helps!

Fragmentation: The scattering of parts of a file throughout a disk, as when the operating system breaks up the file and fits it into the spaces left vacant by previously deleted files.

That’s how the dictionary defines fragmentation, which can be a very real performance problem for certain databases. But when is fragmentation truly a problem, and how can you deal with it? The folks posting on the SQL Server Storage Engine blog have a nice series on fragmentation that can help debunk some myths and show you how to evaluate and fix problems once they arise.

On that note, Kalen Delaney has also posted some good information on why going to lengths to defragment system tables is often effort best expended elsewhere. See her posts on this subject here and here.

Finally, you can read the MSDN article Microsoft SQL Server 2000 Index Defragmentation Best Practices for the official word on the subject.

SQL Server’s memory limits are determined both by the Operating System limits and by the Edition of SQL Server. You should read the following articles to understand the differences between SQL Server 2000 [Edition] Features and SQL Server 2005 [Edition] Features. Then, you can move on to Server Memory Options and How to configure SQL Server to use more than 2 GB of physical memory.

To see how much memory your SQL Server is consuming, you can use Performance Monitor, specifically looking at the SQL Server: Memory Manager: Total Server Memory (KB) counter, among others. A full article is available on MSDN called Monitoring Memory Usage.

Hope that helps.