Patrick Purviance – SQL Server DBA Blog

Have you checked your TempDB data file count and sizes?
And moreover, are you monitoring its performance?

A general rule of thumb for most DBAs, for best TempDB performance in SQL Server, you should have one TempDB data file for each processor core in your SQL Server and each of those data files should be the exact same size and Autogrowth turned off. The latter point is very important for correct and balanced use of the data files allocated to to the database in that filegroup, but the former point is kind of a generalized practice to make it easier on yourself when setting up a new SQL Server. We even once had an onsite Microsoft Server Analysis rep tell us we should allocate one data file per CPU, but experience tells me this isn’t always the best solution. You really need to monitor your TempDB utilization and determine what you need. If this wasn’t TempDB, how would you determine if you needed more data files for your user databases? You’d monitor performance, right? Looking for certain wait types, right? Sure you would.

See a fantastic write up by Paul Randal (SQL Server MVP and Microsoft Regional Director) below.

Seems I was quoted in another SSWUG email daily newsletter a while back. The topic of conversation for quite a few days was index fragmentation and how to handle it.

The link above may not work well for everyone as I can’t find a way to get a bookmark to take you directly to the quotation, but below is the original topic and my response quoted in the newsletter on 07/07/2010.

A recent request from the SSWUG email newsletter asked how we as DBAs approach and handle application/service consolidation on your SQL Server servers. I have a pretty strong feeling and approach to this topic and emailed my experience to SSWUG…..and to my surprise (for the 2nd time I just heard from another DBA friend of mine), I was “published” in the daily SSWUG newsletter. Even further to my surprise, my entire email reply was included in its entirety. Thanks SSWUG!!

And just in case the link doesn’t work someday, here’s the text of the originally posed topic and my response.

-Patrick

I threw together a SProc for returning useful information about long running operations for SQL Server 2005+ and thought I’d share it. The procedure uses the dynamic management objects, dm_exec_requests, dm_exec_connections, dm_exec_sessions, and dm_exec_sql_text to tell you how much longer you may have to wait on processes that SQL Server categorizes as “long running”. Typically I use this SP to find out how much longer a backup or a restore process is going to take. And it’s usually within a few minutes of being correct, so it’s worlds better than starting a restore on a SQL 2000 box and just telling everyone it will be done when it’s done and there’s little way to tell other than experience.

Let me know what you think…

-Patrick

Just posting a fantastic resource link today.  SQLSecurity.com keeps a nearly always up to date listing, complete with links to KB Articles and the MS product pages, for SQL Server Versions/Builds.  This one is my personal favorite of all the SQL Server build lookup pages online.

http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx

--Chip

I finally broke down and decided to open a Twitter account and hope to use it professionally to share SQL Server knowledge. I don’t have enough interesting to follow yet, but I’m hopeful I can find a way to begin leveraging both the blog and the Twitter account to become more involved in the SQL Server community.  If you do begin to find my musings and tips helpful, follow me on Twitter here.

SQL Server 2008 does away with the dedicated Surface Area Configuration Tool included with SQL Server 2005 in favor of leveraging a new facet within SSMS (SQL Server Management Studio) to now expose the properties necessary to make changes to options not available through the 2008 SQL Server Configuration Manager.

The new facet is called Surface Area Configuration and is accessed via SSMS by right-clicking on the instance name and choosing Facets.  You then select Surface Area Configuration from the Facet Drop-Down box and there you go.  Access to some feature options that you might have thought you lost after upgrading to 2008, but low and behold, Microsoft still has you covered!  Interestingly enough, this works as a single replacement alternative for the 2005 Surface Area Configuration Tool as it works with SQL Server 2005 instances as well (if you’re using SSMS 2008).

Make sure you dig around a little in the View Facets form as there are some more interesting nuggets hiding in there for you to discover….

I’m hoping to either find or startup a local MS SQL Server Users Group.

I’ve not found anything to date in the Wichita Metro, so I’m looking for interest in gathering with local SQL Server DBAs for tech discussions, networking, and possibly bringing in special speakers.  If you are a DBA in the Wichita Metro area and would be interested, or know of someone who is, please drop me a line via the contact page and let me know.  I’m not sure how quickly we could pull something together, but with the right people, anything is possible.

If you’re lurking, subscribe to this posts comments (or my entire blog feeds) and I’ll try to keep the progress up to date.

Thanks for tuning in…..

Quick check for stored procedures run at startup.  This will help you determine what stored procedures you may have set to run at startup.  I use this quite often (in modified form) to locate SQL Servers in the domain, leveraging SQL Server CMS (Centralized Management Server), running the SQL Blackbox trace.

Tested to run on SQL 2000/2005/2008

exec master..sp_MSforeachdb
'Select ''?'' [Database Name], name as [Startup Proc Name]
From [?]..sysobjects
Where type = ''p'' and OBJECTPROPERTY(id, ''ExecIsStartup'') = 1;'