Search results matching tags 'SQL Programming' and 'Best Practices'

Dev Advice: Make a Tiny Dev Database Act Like a HUGE Prod Database

KKline — Fri, 16 Mar 2012 14:30:00 GMT

Here's an evergreen question. It's a question that never completely goes away. But lately, I've been getting it a few times per week. So I thought it's time to readdress the question, which usually takes some form of the following:

I can't really do effective development on my little dev laptop because our production SQL Server database is 15 gazillionbytes, way too big for my workstation. What's a uber-nerd to do?

Well, maybe they didn't use the word "uber-nerd". But you get my drift, right? The production database is really, really big - unmanageably big for keeping a local copy. So that means the dev either has to create a metadata-only version of the database, which won't produce realistic query plans, or somehow crush their laptop under 15 gazillionbytes of MDF and LDF files.

Actually, you have a better alternative - a clone database, sometimes called a shell database. Here's how I described a cloned database a few years ago here in my Tool Time column for SQL Server Pro Magazine:

In effect, a cloned database includes all of the schema objects of the database (e.g., tables, views, stored procedures), as well as the statistics and histograms (the so-called "statistics blob"). This metadata is quite small by volume but can tell you what estimated query plans look like outside of a large production environment and how those estimated query plans might change when SQL Server is upgraded. Cloned databases are especially useful when the data is confidential, classified, or subject to privacy laws.

The article gives you all the detail you need to effectively and quickly create a small version of a big, ol' production database that produces the same query execution plans as you'd get on the prod server.

If you're struggling with doing development on a big SQL Server database, learn the ropes on cloned databases asap! You'll be glad you did.

Enjoy!

-Kev

-Follow me on Twitter

Database Maintenance Scripting Done Right

KKline — Fri, 18 Feb 2011 13:16:00 GMT

I first wrote about useful database maintenance scripts on my SQLBlog account way back in 2008. Hmmm - now that I think about it, I first wrote about my own useful database maintenance scripts in a journal called SQL Server Professional back in the mid-1990's on SQL Server v6.5 or some such. But I digress... Anyway, I pointed out a couple useful sites where you could get some good scripts that would take care of preventative maintenance on your SQL Server, such as index defragmentation, updating statistics, and so forth. One of the script kits came directly from Microsoft's internal database management team. But, alas, they haven't published any updates in quite a while. On the other hand, the other set of scripts came from Ola Hallengren, who has done a great job keeping his scripts up to date. Recently, Ola added support for updating column statistics, both in a generalized update and also updates for only those columns whose statistics have been modified. He's added some other goodies to the latest release, which you can read about here, http://ola.hallengren.com/Versions.html for all details. In addition, I'd like to remind you of the white paper I wrote a while back called Automating DBA Processes, which covers many aspects of database automation and cherry picks some of the best practices from many different thought leaders, such as Ola, Michelle Ufford (blog | @sqlfool) who will be speaking at the 24HOP soon, and Allen White (blog | twitter).

Looking for Good DMV/Database Admin Queries!

KKline — Tue, 07 Apr 2009 21:19:00 GMT

I like to collect useful database administration queries that leverage the SQL Server 2005 and 2008 DMVs. Heck, I'm still interested in SQL Server 2000 queries too. I thought I'd make my search public so that a) you can share your favorite queries here or great reference queries written by others and publicly posted on the Internet, and b) everyone can benefit from this collaborative approach to DMV queries. If you're aware of collections of scripts, for example like those available from the SQLCAT team, please post the location of the collections or libraries.

The intent is to provide ourselves with a set of scripts they can use to perform tasks that would otherwise require them to hit BOL heavily to research what DMVs or system catalog views they need to access to get what they want. These types of activities are not easily performed from within the query tool user interface.

I’m requesting everyone to post or reference your favorite queries in any of the following categories below. The queries could be in your notes, from web sites like MSDN or TechNet or SQLServerPedia.com, from our great SQL Server bloggers and MVPs. (Be sure to give credit to the originator when you post it here.) Speaking of favorite scripts, you might want to check out SQLServerPedia.com, if you haven't already done so. The wiki is getting quite large and there's lots of new information popping up daily. Looking for more good query samples? If you didn't already know it, be sure to check the Samples folder in your SQL Server installation. Microsoft has a lot of examples in their SQL Server 2005 Script Library.

When posting, please:

· Describe briefly what the snippet does

· Describe if this is a 2005/2008 query or just 2000

· Provide the SQL / Script and indicate if there are any parameters or if the SQL can be run without modification

Here are some categories I'm looking for, but if you have something not addressed here, please post it:

· Object Sizes – a list of objects in a database with their sizes

· Missing Indexes

· Index Utilization – all indexes

· Index Utilization - on a specific table

· Index Fragmentation – all indexes

· Index Fragmentation – on a specific table

· Index Defrag options – various with defrag, rebuild, online, offline, heap, etc.

· SQL Performance – leveraging the 2005+ DMVs for worst performers, active statements

· CPU and Optimization

· Buffer Cache

· Wait Stats

· Deadlocks

· Plan Guide Queries

· SQL Trace

· Backup History – or other backup related queries

Thanks in advance!

-Kevin

Webcasts on-line

KKline — Thu, 08 Feb 2007 14:02:00 GMT

As I've been traveling around speaking at various SQL Server user groups, I get requests for my slide decks. Of course, I'm happy to share those, but I've got one better for you. As it turns out, you can find five of my most recent webcasts here. These are recorded webcasts that you can listen to any time. I believe that registration is required. The topics are:

- Ensure Optimal Performance in Deploying SQL Server 2005
- Worth Upgrading to SQL Server 2005
- SQL Query Tuning
- Disk Optimization and I/O Tuning on SQL Server 2005
- Best Practices for Stored Procedures Presentation

I'd love to hear your questions or comments about the sessions. Post comments here or email me at kevin_kline@sqlpass.org.

Enjoy,

-Kevin