Patrick Purviance – SQL Server DBA Blog
Blogging impactful solutions for SQL Server DBAs worldwide

Archive for the ‘Odds and Ends’ Category

Another SSWUG mention found…

Wednesday, July 14th, 2010

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.

The Question:

Index Defragmentation – How Often Do You See the Need?
Once again, on the list servers here on SSWUG there is a solid discussion about index defragmentation and an application that sets up maintenance plans.

First, a question – please take just a minute and let me know – how often (on average, yes I realize it is variable) do you find yourself needing to defragment your indexes on your production databases?

My response:

…and here’s Patrick’s experience – “We have a variety of systems that we support (120 servers/150 instances (2000/2005/2008)) and my experience shows that for many systems we are able to simply ignore the fragmentation levels because the performance of the system is not negatively impacted enough by the fragmentation to become a squeaky wheel. That said, however, some of our largest systems (0.5TB to 0.8TB in size with tables approaching 120GB) require outages and defragmentation every 6 months to keep performance at an acceptable level. We have a custom built SProc that we run on a weekly basis on the majority of the systems that sends us an email based report on the fragmentation levels of the indexes and also has internal logic to identify what actions should be taken based on best practices (defrag/rebuild/drop & recreate). This rarely gives us any grief and I feel we’ve effectively addressed fragmentation from a performance standpoint enterprise wide.

Yes, you will likely find some heavily fragmented tables/indexes here and there, but by and large we have very few performance complaints that can be tracked down specifically to fragmentation (I feel due to the process we have and use). 9 times out of 10 it’s an I/O bottleneck or someone running a report where it shouldn’t be run.”

I got SSWUG Published ;)

Wednesday, July 14th, 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.

Original SSWUG Topic Question:

Who Shares Your Database Server?

Many small businesses and often times large businesses find it difficult to have servers deployed for a single purpose. They may be constrained by hardware and software costs, or not have the necessary skills to configure servers according to best practices.

For example, Microsoft encourages the use of integrated security for Sql Server. Best practices teach us that your Domain Controller and Sql Server should reside on different instances of Windows Server. Already we have two servers.

IIS running on Windows, or even Apache, are often best when run on a different server. This is especially true if the web server faces the Internet. In that case, you will most likely have a firewall between your web server and all your internal hardware, and therefore can’t share windows with Sql Server. Even web servers that are for internal purposes only should not share resources with your database.

Other products also compete for resources that are often used in conjunction with Sql Server. SSIS, SSAS, and SSRS all may require significant memory, disk or CPU resources. Many businesses also have Mail servers, application servers, etc.

Let’s hear from you. You live in the real world and have to balance resources with business needs on a daily basis. Why not share things like what you are currently doing, what you like about it, what you would like to change, and the factors that caused you to choose your current configuration. Don’t worry if your system doesn’t follow best practices. The goal here is to share with each other the issues we all face. Send your experience to

My published response

Patrick Says…

We have a decent number of dedicated SQL Server instances (hundreds) to support all of the different business applications utilized in our environment. Server sprawl and SQL Server “Silos” as they’ve been labeled by the Systems Admins, have become more prevalent over the last few years, and to accommodate, most of our web/application/support service servers are run in VMs. We also run our SSRS and SSAS installations on dedicated servers because the required resources to do what they’re intended to do without causing performance grief.

I’ve always been a firm believer that you must hold your ground on providing the most appropriate environment for your SQL Server databases to perform within if you have any chance of meeting rigid SLAs (written or not, expressed or implied), and rarely, if ever, allow any other applications that consume even remotely moderate amounts of memory to be run on my SQL Servers. I’ve seen too many departmental SQL Servers struggle to breath with IIS\Exchange on them and just don’t allow it unless there’s no other way, and I document my stance early on.

SQL Server requires certain things. CPU, Memory, Storage. I rarely run into CPU issues with todays (and even yesterday’s) processors. I rarely spec a server with less than 4GB of RAM and usually much more, but there are a ton of configuration options/needs there. The I/O subsystem usually is where I have to compromise most, but that’s a whole different conversation. But right now we’re talking about role/application consolidation, and to me it comes down to how much RAM and CPU is available for SQL Server with at least 30% overheard (with a little flexibility). If there’s anything left in a departmental/dev/test server, we’ll talk about it. If it’s production, fuggeddaboutit!


Do you Tweet?

Thursday, May 27th, 2010

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.