« SQL Server 2008: Day 1 | Main | Mythological reputations »
SQL Server 2008: Day 2
By farseeker | August 13, 2008
There’s a fantastic feature in the Management Console that should have been there since the very beginning. I didn’t find it yesterday for reaons that will become apparent in a few minutes:
Intellisense! Its about time. There were third-party plugins that did the same thing, but seeing as I don’t use the query designer to design queries most of the time (Imagine that! This is actually done in the engine we use, see a previous post), so when I do go to the query desginer to do a query it’s usually something very complex, involving alias’s. And as soon as you throw an alias name against a COUNT field it all seems to go to shit (alias names against tables and real columns are fine. Alias names for tables show correctly but alias’s for fields do not show. Maybe this is because you can’t order by an alias on a real field?).
Execution Plans seem to be much of the muchness, but there is a new button “Set Query Options”:
Allowing all sorts of options for this query. You can change the display output, include login credentials for multi-linked-server-queries, and all sorts of useful things.
For those who are wondering what the different output types are, there’s Grid and Text. Nothing new there, but if you’re still wondering what the difference is, obligatory screenshit (hehe I said screenshit) is below:
There’s a few other new buttons, but the most interesting by far is the Debug button (in fact there’s a whole Debug menu that has most of the standard Visual Studio debugging commands). It seems to start a Visual Studio debugging session whilst running the query:
That’s deliberately a very poor query, running an order on 750,000 rows without an appropriate index. I’m not entirely sure what this debugging thing does however, because I can’t seem to get it to do anything. Maybe it’s for Stored Procedures or something, becuase you can even set breakpoints. We only have two stored procedures, and both of them are used for dynamically directing phone calls when they hit our IP PBX based on the customer’s current status, so maybe not such a useful feature for me. For developers who do a lot of DMBS-level business logic perhaps this will be a life saver.
Another interesting debugging tool is the ability to be able to trace a single command through the SQL profiler. Again something that’s not hugely useful for myself, but a Stored Procedures guy would find that incredibly useful.
[Off Topic: I found the most peculiar thing yesterday. In our management tool (not this one), according to the SQL Profiler, if we browse a table using the first index (not always the PK), it creates a cursor, loads enough records to fill the viewport, pauses the cursor and waits for the user to request a record that's not in the viewport. Fairly normal behaviour there. But the really strange thing is if we then change the index to run on an index that's not the first one it does a straightforward SELECT query, which means it needs to return ALL the records in the table. It then throws away that select, creates a cursor, and fills the viewport. Which means on a tables with a LOT of records you need to return the entire results set, just to have it thrown away. Stupid].
I can’t use the Database Engine Tuning Advisor at the moment a one main reason: I’m on the train and I’m not wanting to kill my battery. And I like to do online advisories too. Strangely (or perhaps not so much for those that know me), my favourite feature about the Tuning Advisor is when its running it has a step that says “Consuming Workload”. I have this mental image in my head of a hungry hungry hippo eating all my work (and hopefully my ToDo list). Now that’s a better excuse than “The Dog Ate It!”
Topics: General | No Comments »