Search results matching tag 'SQL Programming'

Help Me Update the History of SQL Server

KKline — Wed, 12 Sep 2012 15:01:00 GMT

I was chatting with my buddy, Buck Woody (Twitter | Blog), about a week ago and we were discussing that it's pretty hard to put together the entire history of SQL Server. Then the thought hit me that I'd already done this, to a degree, in my book Transact-SQL Programming. This was the first Transact-SQL programming book on the market way back in the SQL Server 7.0 days and even included full coverage of both Microsoft and Sybase variants of T-SQL.

The thing is, I was never able to put out a second edition due to some legal and contractual issues. So, help me catch up on the history of SQL Server:

The Ancient History of Microsoft SQL Server

SQL Server, like many things related to SQL, is a story of diversity. At one time, both Sybase and Microsoft had virtually the same product with Sybase's product targeted at Unix and enterprise environments while Microsoft targeted only the Windows enterprise. Today, the two products are entirely divergent. The code base for each product is unique to each platform and shares no code at all. For a while, both Sybase and Microsoft called their flagship database product "SQL Server", but now, Sybase calls their implementation of the product Sybase Adaptive Server Enterprise. So while the two products are now completely distinct, they continue to share a strongly similar Transact-SQL implementation. Other less noticeable similarities persist, such as the use of Tabular Data Stream (TDS), tempdb architecture, and a few other odds and ends.

Perhaps you want to know more about how things got to be the way they are? By reading this section, you will be making an admission that many programmers and developers are loath to - history is interesting ... perhaps even enjoyable. This is not actually a sign of weakness as some might attest. With tongue planted firmly in cheek, please read on. Table 1-1 shows the evolution of both Microsoft and Sybase’s version of SQL Server’s.

Notice that I stop at more than a decade ago. Help me add the subsequent details by leaving a comment! Extra points if you know the code name for each release.

1987 - Microsoft Buddies Up with Sybase

Microsoft and Sybase announce a technology and marketing partnership. Microsoft gets exclusive rights to market Sybase’s DataServer product on OS/2 and all Microsoft-developed operating systems. Sybase gets royalties and added credibility in the Unix and VMS markets. (Remember VMS?) Sybase ships their first commercial DBMS product, called DataServer, for Sun workstations running Unix.

It was only 20 years prior that the first Super Bowl was played as the Green Bay Packers of the National Football League defeated the Kansas City Chiefs of the American Football League, 35-10. (Hey, now this is important!)

1988 - Microsoft Buddies Up with Ashton-Tate

Microsoft and Ashton-Tate announce a marketing partnership. Microsoft can’t put a dent in dBase’s tremendous market presence. Ashton-Tate wants access to Sybase’s powerful multi-user database management technology, which ships this year. Microsoft forms a three-way alliance.

Another interesting three-way alliance was also making headlines at that time, as independent counsel Lawrence E. Walsh probed the Iran-Contra affair.

1989 - SQL Server v1.0 Ships

Ashton-Tate/Microsoft SQL Server version 1.0 ships.

Perhaps not coincidentally, NYC transit fare rises from $1.00 to $1.15.

1990 - Microsoft Ships SQL Server v.1. for, OMG, Windows!

Ashton-Tate dBase IV was floundering. Microsoft wanted to beef up its offerings for the new OS/2 LAN Manager product. Microsoft and Ashton-Tate quit the partnership. “Microsoft SQL Server” version 1.1 ships by summer with support for Windows 3.0

Another partnership came to an end that year: ousted Panamanian leader Manuel Noriega surrendered to U.S. forces, after taking refuge in the Vatican's diplomatic mission.

1991 - Microsoft Goes All-In on Windows 3.0

A proliferation of Windows 3.0 front-end tools spurs the growth of Microsoft SQL Server. Later that year Microsoft and Sybase amend their contract to allow Microsoft to make actual bug fixes - all under Sybase supervision. IBM and Microsoft call off their OS/2 partnership with Microsoft to focus on Windows. Sybase surpasses the $100 million revenue mark and goes public on NASDAQ.

Almost one thousand years ago to the day, significant improvements in front-end tools (i.e. the plow and horse yolk) spur a huge population boom in Europe. The Vikings and Europeans call off their partnership, with the Vikings dragging off most of the loot. Meanwhile, Islam is continuing its military expansion throughout the world. Fortunately, medieval Europe does not have to endure lengthy beta programs, although computer geeks have to endure a plague of viruses. No fair!

1992 - Microsoft Goes Nuts on Windows NT

Microsoft and Sybase SQL Server 4.2 ships. Microsoft diverts its attention away from OS/2 and into developing Windows NT. Microsoft SQL Server for Windows NT later ships in beta release while Sybase ships its much-vaunted System 10.

A famous diversion of attention in the political arena, you ask? Then President George H. W. Bush lost his lunch during a state dinner in Tokyo; White House officials tried to deflect attention saying Bush was suffering from stomach flu.

1993 - Microsoft and Sybase Become Unfriends

Microsoft Windows NT 3.1 ships, closely followed by Microsoft SQL Server. By 1994, Sybase SQL Server System 10 and Microsoft SQL Server were competing unabashedly as the two formally ended their partnership. (This is about the time that I started to use Microsoft SQL Server 4.21 on OS/2).

Likewise, the American voting public formally ended their relationship with “Read my lips” President George H. W. Bush.

1995 - Microsoft Enters the Enterprise Computing World

Microsoft SQL Server 6.0, a very significant release, ships in June. Up until this time, most Microsoft products were considered departmental-level only. SQL Server also begins to push out many PC-level database products, with the noticeable exception of Microsoft Access. Access will be a thorn in the side of SQL Server DBAs until ... uh ... probably 10+ years from now.

Other significant release for 1995 included O.J. Simpson - not guilty.

1996 - SQL Server 6.5 Goes Into Production

Though released in April, it’s no April Fool’s joke. SQL Server 6.5 is the most powerful and capable version Microsoft had ever produced. It also received considerable press because of its innovative user interface and free tools.

1997 - Microsoft SQL Server 7.0 Beta Released. Remember the Code Name?

Is there a causal relationship between the Monkees' appearance at the Hollywood Bowl (June 10, 1967) and the first shipment of the Apple II Computer (June 10, 1977) and the release of MS SQL Server 7.0 beta (June 10, 1997)? It does make you wonder, doesn’t it?

1998 - Microsoft SQL Server 7.0 RTM

Microsoft offers another set of new innovations in the SQL Server 7.0 release, including an "all-in-the-box" strategy. Other major DBMS vendors had, and still have, separate products for ETL (extract-transform-load), administration tools, scheduling, and business intelligence. Not Microsoft. With SQL Server 7.0, Microsoft included the Data Transformation Service (DTS) for ETL work, Query Analyzer and other administration tools, OLAP services (my brain gets a little cloudy on this one), along with the already existing scheduler, SQL Agent.

2000 - Microsoft SQL Server 2000

Branding experts said that version numbers should get too big, or else customers would perceive the software as stodgy and old fashioned. So Microsoft switched from the version number nomenclature to the one highlighting the year of the release for SQL Server 2000. This release included the game-changing Enterprise Manager (EM). Forced many other DBMS companies to at least attempt to build useful tools (anyone use SQL*Plus from Oracle). Again, my brain is getting dusty with old age and too many releases, but IIRC this was the release in which Microsoft finally got ride of page-level locking in favor of row-level locking. That shuts up a lot of Oracle-bigots, but doesn't truly ease their smarmy attitude. Grudge matches continue unabated.

I'm going to follow-up with another blog post carrying forward with SQL Server 2000, including highlights for important half-steps like SQL Server 2000 SP3, the "Slammer" security release.

Feel free to throw down some of your SQL Server trivia knowledge about release and features for releases. Share a picture of one of your old boxes, if you have one!

Enjoy,

-Kev

Twitter | Facebook | YouTube | LinkedIn | Blog

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

How Do You SKU?

KKline — Wed, 30 Mar 2011 14:49:00 GMT

I’d like your opinion here.

Follow my logic here for a moment as I walk through a couple rhetorical questions. Have you ever had a friend developed an application entirely on SQL Server Developer Edition? (Not that YOU would ever do such a thing, but maybe you know someone who has. Right?) And has your friend’s IT department actually deployed said application only to discover that they’re only licensed for Standard Edition in their production environment? And then was your friend’s IT management team is horrified to learn that they’ve either got to go through the very expensive process of extracting all of the Enterprise and/or Datacenter Edition features for the production application in order to remain in compliance, upgrade to the more expensive SKU licenses, or risk a potential future audit?

I’m not saying that this has happened to any of us. We’re too smart for that, after all. But have you ever known anyone who’s had this experience?

Having worked with a lot of customers another commercial RDBMS platforms (which I’ll euphemistically call “SEER” from Redforest City and “IB4” from Upstate City), I can tell you that auditing is a fun and exciting way for those platform vendors to make a LOT of money. This is especially true because a production application, once successfully deployed, tends to be too valuable to disable or otherwise compromise because high-end features slipped in to the development cycle even though the production environment only a “standard edition” SKU in place. Ouch! Talk about being caught between a rock and a hard place. Now, keep in mind that this is a strategy used by SEER and not by Microsoft. But Microsoft could implement the same sort of licensing audits if they wanted to. (Please leave a comment here if you have ever been audited. I’d love to hear your experiences, at least as much as NDA’s allow).

So if you use SQL Server Developer Edition (DE), of any version, would you like to see a feature that enables you to run DE not in its default “full featured mode” but at another SKU level, such as good ol’ Standard Edition? I know I would.

If you’re on the same page as I am, there are a number of suggestions logged on Connect about this very feature! Make your voice heard! Check out:

https://connect.microsoft.com/SQLServer/feedback/details/496380/enable-sql-developer-edition-to-target-specific-sql-version

Of course, the more skeptical reader might say “Hey, that’s their tough luck. Developers should know the difference in the SKU licensing options and feature sets of whatever SKU they’re developing on compared to what they’ll deploy on.” And I wouldn’t fault you for saying so.

But I would go on to point out that much of Microsoft’s success in enterprise IT settings can be traced back to their very strong relationship with developers. And anything that Microsoft can do to empower developers to save time, money, and resources during the development phase of an IT project in turn energizes that relationship between developer and Microsoft.

It also makes the life of the DBA that much easier, because they don’t need to imply that those cowboys on the development team went off half-cocked again. So what’s your opinion? Should SQL Server Developer Edition include a feature that sets the SKU-level of the database engine?

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).

Join Me May 19th for 24 Hours of PASS

KKline — Mon, 26 Apr 2010 14:16:00 GMT

Join Us for 24 Hours of SQL Server Training

(with a special focus on SQL Server 2008 R2)

Did I mention that your Cohorts in Crime (that be me and Brent Ozar) are presenting?!?

The FREE 24 Hours of PASS event is bringing an exceptional lineup of SQL Server and BI experts to your computer starting at 12:00 GMT (UTC) on May 19.

Get an in-depth look at the hottest SQL Server and BI topics, including (but not limited to!) - the new SQL Server 2008 R2, with its business intelligence and data management innovations, and much more.

When does it start? 12:00 GMT (UTC):

New York: 08:00
Chicago: 07:00
San Francisco: 05:00
London: 13:00
Paris: 14:00
Moscow: 16:00
Mumbai: 17:30
Singapore: 20:00
Sydney: 22:00

The roster of phenomenal speakers features many MVPs and top-rated presenters, including Adam Machanic, Andy Leonard, Brad McGehee, Brent Ozar, Brian Knight, Chuck Heinzelman, Dean Richards, Don Kiely, Don Vilen, Donald Farmer, Glenn Berry, Greg Low, Jacob Sebastian, Jessica Moss, Kevin Cox, Kevin Kline, Louis Davidson, Maciej Pilecki, Peter Myers, Peter Ward, Rushabh Mehta, Sean McCown, Simon Sabin, Thomas Grosher.

Check out all the great SQL Server sessions you can attend for FREE. Share this information with a friend or colleague.

PASS is looking forward to having you join us all for this exceptional event. Please contact us at 24hrs@sqlpass.org with any questions. You can also find lots of general details at http://www.sqlpass.org/24hours/2010/.

2009: The Year in List Form

KKline — Tue, 05 Jan 2010 18:53:00 GMT

Before I jump onto the Goals and Themeword meme started by my buddy, Thomas LaRock (blog | twitter), I decided I'd spend a few minutes looking back on both the year 2009. (From a personal standpoint, the 00's were my most difficult decade yet. Major problems of every stripe beset me on all sides and with alarming frequency throughout the decade. I was all "Good Riddance" and "Don't let the door hit y'ass on the way out, 2009!" as the ball dropped in Times Square.)

Rather than spend a lot of time cooking up my own top 10 lists, I reckoned (that's Southern for "thought", btw) I'd recap a few others top X lists that are in the ballpark of my own personal opinion. I couldn't resist putting together my own list at the end, which I'd love to hear your thoughts on. In addition, I want to hear about your Top 10 (or 5 or 3) for 2009!

Their Lists

Time Magazine's list of Top 10 of Everything 2009 was a pretty good recap for the year on big ol' cultural touch points like movies and music. I found at least one thing to agree with in each of their pop culture lists: movies - The Hurt Locker - check; TV shows - my personal favorite for its brilliant cohesive multiyear storyline and excellent character studies, Lost - check; album - I and Love and You by the Avett Brothers - check; books (sigh - if only I had more time) included the wonderful The Age of Wonders by Richard Holmes.

Lifehacker's Top 5 Hive Topics of 2009 is a very interesting list covering lots of topics and pointing out a lot of interesting tools that I hadn't encountered before. My personal favorite among them was the Top 5 Alternative File Copiers, since the Windows Explorer copy feature reminds me of hungrily awaiting my food in the microwave and just as the counter gets to the T-10 countdown, it goes back up to 30, then down to 8, then back up to 42, then down to 14.

I'm not sure if they meant to be funny but Digg's Top 10 Most Popular Stories of 2009 is hilarious, much in the same way that Brent Ozar (blog | twitter) and I were when we put on an unintentionally hilarious performance at the PASS 2009 Summit Quiz bowl. Go ahead - ask Colin Stasiuk (blog | twitter) what he thought of our performance...

Impressed by Our Quiz Bowl Performance?

Speaking of unintentionally funny, have you seen Yahoo's Top 10 Searches of 2009? Evidently, the median Internet user (at least from their metrics) is hormone-laden, teenage redneck with a thing for fast cars (Nascar), Hollywood hotties (Megan Fox), and an unassailable but secret love for Mormon-influenced Vampires (Twilight).

My List

I usually try to blog at least once per week and, when I can, even more. I still have this deep down urge to post lots of small blog posts of just a couple paragraphs. But for some reason, I always seem to come out with these big ol' epistles. Despite my verbosity, y'all still read what I write and for that I'm very thankful. Over the last year, these were my top ten blog posts according to your interest:

Best of the [SQL Server] Blogs and its sister post Great Blogs from the Microsoft SQL Server Teams, also my number one spam generators. Ever spammer on the planet seems to want their comment appended here.
Microsoft Resources Too Good Not to Share, which I can't honestly remember if they were any good or not. But I bet they were.
Are We There Yet, Mom? in which I flashback to my childhood road trip experiences when considering Microsoft's overall product strategy.
Does the Down Economy Have an Impact on Your Job, cuz it sure punched mine in the mouth.
Why Do I Keep Seeing This Mistake, in which I learn that "Hello World" type applications can lead to massive misunderstandings.
Microsoft [Corporate] Marketing Throws SQL Server Under the Bus. We can't get no respect, not even from corporate HQ.
Looking for Good DMV Database Admin Queries, where you can find just about every good DMV query ever written except those other really good ones that are posted here in the comments.
Old Performance Recommendations Die Hard, and when I saw "die hard" I don't mean like Bruce Willis.
Things You Know Now, a semi-successful meme I started where I asked participants to tells us about stuff they'd do differently if they knew it way back in the day.
Understanding SQLIOSim Output, because no one seems to fully understand this tool, including me.

I excluded a few posts that were numerically in the top ten because, well, they're my blog posts and I didn't want them in the top ten. So there! But those that I excluded were things like reposting an interview done by another blogger or maybe a product or book that I plugged for some reason or an other.

I hope you've enjoyed my blogging and found it valuable. Tomorrow, I'm jumping on the themeword and goals meme. In fact, I'm crashin' the party because none of my peeps called on me. [pout]

Be well!

-Kevin

Twitter @KEKline

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 <!--[if gte mso 10]> http://sqlblog.com/blogs/kevin_kline/archive/2007/06/28/understanding-sqliosim-output.aspx

Upcoming Seminar and SQL Saturday in Florida, Oct 13-17

KKline — Sun, 04 Oct 2009 21:24:00 GMT

Please join me for SQL Saturday #21 coming up on Saturday, October 17th. There are over 50 sessions on tap with great speakers from the area and across the nation. Some of the notable speakers include:

Andy Leonard (blog here)
Andy Warren (blog here)
Brian Knight (blog here)
Buck Woody (blog here)
Chad Miller (blog here)
Joe Celco (blog here)
Joe Webb (blog here)
Jonathan Kehayias (blog here)
Kendal Van Dyke (blog here)
Ken Simmons (blog here)

Not only do we have SQL Saturday coming up, but Andy & company put together five world class seminars the week leading up to it! Use code "KKLINE" to get a 20% discount if you register for two or more seminars!

Oct 12 - Essential SQL Server Administration By Andy Warren
Oct 13 - Learn BI In a Day by Brian Knight
Oct 14 - Real World Performance Tuning by Kevin Kline
Oct 15 - Zero to SSIS by Andy Leonard
Oct 16 - Performance Tuning Methodology by Buck Woody

Seminars will be held at the Embassy Suites in Altamonte Springs, FL. Each seminar is $149 and includes a full lunch. Seating is limited, register today!

Attendees of my seminar will get a free copy of my book, Database Benchmarking: A Practical Approach for Oracle and SQL Server, plus other free goodies from my employer, Quest Software. As an added note of explanation, Buck's excellent seminar centers around detecting and troubleshooting performance problems, while my seminar focuses on writing high-performance and highly scalable SQL and Transact-SQL code. Why not sign up for both of them to get comprehensive education on the entire process of writing great code, deploying it, and monitoring it for continued excellent performance?

Finally, I'll be speaking at user groups in the area leading up to SQL Saturday. If you can join me at my seminar or at SQL Saturday, maybe one of these other events better fits your schedule:

Tuesday, Oct 13, Tampa SQL Server User Group (http://www.tampasql.com)
Wednesday, Oct 14, Orlando SQL Server User Group (http://www.opass.org)
Friday, Oct 15, Jacksonville SQL Server User Group (http://jacksonville.sqlpass.org)

I h0pe to see you there!

-Kevin

Twitter @KEKline

Bitemporal Data

KKline — Tue, 04 Aug 2009 14:12:00 GMT

Any IT pro with more than a year or two of experience will have faced the challenges of version control for an application, but what if you have to implement version control for data?

The most common way to tackle this problem is implementing something called "bitemporal data". Under this method, each row in a table includes the current valid time and the transaction time. Since two distinct time values are stored, we get the term bitemporal.

A great place to start is Adam Machanic's excellent article at http://www.simple-talk.com/sql/t-sql-programming/a-primer-on-managing-data-bitemporally/. In addition, I encourage you to check out Adam's book "Expert SQL Server 2005 Development" at http://www.apress.com/book/view/9781590597293.

In addition, Dejan Sarka pointed out that you can get a very comprehensive information on supporting temporal data from a book entitled "Developing Time-Oriented Database Applications in SQL" by Richard T. Snodgrass. Mr. Snodgrass was so kind to publish the book in PDF format and make it available for free download at http://www.cs.arizona.edu/~rts/publications.html. (Note that the book is quite old now (year 1999), so the T-SQL code does not include any SQL Server 2005 or 2008 enhancements. But the basic SQL is still solid.

Cheers!

-Kevin

kekline @ twitter

P.S. Check out my new site - http://kevinekline.com

Why Do I Keep Seeing This Mistake?

KKline — Fri, 10 Jul 2009 17:31:00 GMT

One of the fundamentals of loop optimization is that you should move stable operations outside of the loop. What I want to know is - if this is such a fundamental rule, why do so many people break it?!?

If you are familiar with other programming languages, then you are probably aware of loop optimization techniques. You should try to put all operations outside of the loop if they don’t need to change within the loop. This reduces the amount of unnecessary repetitive work. SQL Server optimizer doesn’t automatically recognize such inefficiencies and clean the code for you (compilers of some other languages do). You have to write efficient loops yourself as in the following example.

These scripts print a table of square roots for all numbers from 1 to 100. Notice the boldfaced code below:

-- Loop with code inside = inefficient

SET NOCOUNT ON
DECLARE @message VARCHAR(25), @counter SMALLINT
SELECT @counter = 0
WHILE @counter < 100
BEGIN

SET @counter = @counter + 1
SET @message = REPLICATE( '-', 25 )
PRINT @message
SET @message = str( @counter, 10 ) + str( SQRT( CONVERT( FLOAT, @counter ) ), 10, 4 )
PRINT @message

END

-- Elapsed time: 376 ms

Compare the above Transact-SQL script to the one below, where the boldfaced code is moved outside of the loop:

-- Loop with code outside = efficient

SET NOCOUNT ON
DECLARE @separator VARCHAR(25), @message VARCHAR(25), @counter SMALLINT
SELECT @counter = 0, @separator = REPLICATE( '-', 25 )
WHILE @counter < 100
BEGIN

SET @counter = @counter + 1
PRINT @separator
SET @message = Str( @counter, 10 ) + Str( SQRT( CONVERT( FLOAT, @counter ) ), 10, 4 )
PRINT @message

END

-- Elapsed time: 36 ms

The second script executes the REPLICATE( ‘-‘, 25 ) function only once, compared to 100 times in the first script. Results produced by both scripts are identical:

-------------------------
1    1.0000
-------------------------
2    1.4142
-------------------------
3    1.7321
-------------------------
4    2.0000
. . .
. . .
. . .
-------------------------
99    9.9499
-------------------------
100   10.0000

Of course, there are a million and one ways to perform any such algorithm. But I'm still surprised that otherwise experienced and competent database programmers are still embedding very stable elements of their code inside of extensive looping operations rather than outside of them. Thoughts?

Cheers,

-Kev

Twitter @ kekline

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