I’m working on updating my book, Query Performance Tuning Distilled, so that it reflects the new things available in SQL Server vNext:Denali. I’m going through the first chapters that are all about gathering information about your systems. Performance tuning is all about building up knowledge of how the system is working in order to understand what you need to change in order to improve it. I’m surprised by how much hasn’t changed. But some of the changes are fundamental and huge. Let’s talk huge. Extended Events is huge. Extended Events came out in SQL Server 2008, but very few people, myself included, paid much attention. Those who did found the implementation awkward and confusing. Only a few people persevered enough to discover just how powerful and amazing these things are.…
Have to complete my assignment from the Rockstar this month since I’ve missed the last several (he gets all weepy, it’s not pretty). The question this time is to describe your favorite PASS moment… Wow! And this is Tom’s idea of an easy assignment. How do you choose? Lots of stuff flashes through my head, a football helmet with antlers & the song Funky Cold Medina, learning Policy Based Management from Buck Woody (blog|twitter) while simultaneously laughing my ass off, a pile of neatly folded clothes in a bush, friends old & new, speaking for the first time and running out of material 15 minutes early, sessions with great information from really smart and helpful people, Dr. Dewitt nuff said, sitting down to breakfast with people that wrote books I’d…
Did you ever accidentally close SQL Server Management Studio? And, in closing SSMS, did you get the prompt that says “Save changes to the following items?†And did you, completely unthinkingly, with a query you had just been working on, hit Cancel? Yeah, me neither. What kind of idiot does that…. OK. I confess. I just did that. Silly thing it was, but I had just spent at least 1/2 an hour working on a query and now it was gone…. or was it? I had just run the query and had been looking at the results when I closed SSMS. Initially, I panicked and started thinking about how I could get the data back (somewhere there’s a file I’ve heard). Then it occurred to me, I had just been…
There’s a project over on code plex to come up with a mechanism for validating databases and generating the necessary scripts to allow you to move those databases, and data, over to SQL Azure. It’s called the SQL Azure Migration Wizard. There’s no real install yet since it’s just a beta. You can download the executable and run it. It’s pretty straight forward stuff. It’s primarily focused on validating that there is a database, script or series of calls (from a trace event file) that will be properly compatible with SQL Azure, but it can also generate a deployment script from the database. Here’s the opening screen: Not much to it. I’ll walk you through the Analyze and Migrate path which covers most of the functionality of the app. Clearly,…
It sure seems like there’s a lot of miscommunication between developers and database specialists. In fact, the communication can become so poor that outright hostility between the groups is common. At the end of the day we are all working towards a common goal, to add value to whatever organization we are working for. It's a shame that we all lose sight of this commonality and create such a false dichotomy between the groups. I think there are some ways that we, as database specialists, can use to attempt to cross that gap. Prior to being suborned to the dark side, I was a developer. I had a little over 10 years experience working in VB, Java & C#. I remember, distinctly, cursing our database team for being so problematic…
Nice to see most of you have managed to fight your way through the shoggoths outside to attend another lecture at the Miskatonic branch of SQL University. This will be the third and final part of the introduction to indexes lecture. Please, if you're going mad, step out into the hall. Our previous two lectures introduced the concept of indexes and then talked about two types of indexes, clustered and nonclustered. This lecture will cover the concept of statistics as they relate to indexes. If you followed the previous lecture then you know that indexes are stored in a Balanced Tree or B-Tree structure. You know that this storage mechanism is intended to provide fast retrieval of data. But, how can the query engine inside SQL Server know which index…
You just have to love Red Gate tools. They find the small area that they want to cover and then they cover it extremely well. I rave regularly about SQL Prompt and SQL Compare and SQL Search (free one, btw). I've got SQL Data Compare and SQL Data Generator open & working on my desk regularly. I'm dabbling in their other tools fairly often as well. I just like Red Gate tools. I guess my constant & consistent praise is why I'm a "Friend of Red Gate." I like to mention that before I start praising their tools some more, just so no one thinks I'm hiding it. Why would I hide it? I'm proud to say it. I am a Friend of Red Gate! ... anyway... where was I... right, new software.…
I'm working with PowerShell, and digging it. I decided that I wanted to create a new script (blog post later, if I get it to work) and I wanted to try out different scripting tools to build it. I started with PrimalScript from Sapien. It's slick. It's powerful. It has very nice code completion, a great GUI, integration with source control out of the box. I started scripting and everything was fine. Then, I needed to run invoke-Sqlcmd so I decided to change the profile on the shell so it would load the SQL Server cmdlets automagically. I started getting the error: Get-ItemProperty : Cannot find path 'HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps' because it does not exist Hmmm... weird. I'm pretty sure I used the same command on my workstation as on my laptop.…
We've been running the Enterprise Policy Management tools available from Codeplex for a few months now (Thanks to Buck Woody's (blog | twitter) session at the PASS Summit). They're honestly great. It's a fantastic way to use Policy Based Management on 2000 and 2005 servers. We did hit some issues with timeouts and looking at the script, it made a call to invoke-sqlcmd, but didn't pass the -querytimeout value. That means it default to 30 seconds and the import to database process was taking more than a minute for some of our queries. I did a little looking around and decided to just disable the timeout by passing a value of zero (0). But, I still got timeouts. Finally, after a bit of searching around, I found a closed (because…
I'm right in the middle of moving one of our databases from Oracle to SQL Server (and I just love saying that, over & over). Evidently the most common practice in Oracle is to leave all the tables as heaps. That's a tad bit problematic approach for SQL Server. We moved the structure from Oracle to SQL Server with almost no changes and now we're cleaning up the mess fixing the structure iterating the design. I needed to drop & recreate 250 plus indexes. Remember, I'm lazy. A few minutes bouncing around the web and looking at the wonderful book "SQL Server 2008 Administration with Windows Powershell" and I put together a simple little script that generates a script for dropping and recreating all the primary keys on the tables and, as a…