SQL Server News & Information tsql, performance tuning, industry trends, & bad jokes
tsql, performance tuning, industry trends, & bad jokes
This site is maintained by Jason Massie. He has 10 years experience as a DBA and has specialized in performance tuning for the last five. He was recognized by Microsoft as a SQL Server MVP. Jason has spoken at the Professional Association of SQL Server Conference, the North Texas SQL Server Users Group, SQL Connections and TechED. He has worked at Terremark (formerly Data Return) for nearly a decade.
You can contact him at jason@statisticsio.com or 469.569.5965
Abstracts addition Affinity Aggregation allocation Always Analysis Announced another API Appending article Authentication backup be Behavior between Bootstrapper Breaking Build Cache Caching Check checksums Codeplex collection Connecting contest Controller Creating CTEs CTP CUBE cursors Data Database DATALENGTH Debugging Design Diagnosing Diagnostic Differences Documentation DTS Emergency enhancement Entity ETW Exchange execution Express Extensions Fall February Filestream Filtered group GROUPING have Hosting Idle impact Improvement Increase Index Indexes Inserts Instances Interoperability Introduction IO large Late LOB local Localized Magazine Maintaining Maintenance Management maps March Microsoft minutes missing Mix Never November Offline OLE Online operations operators optimizations Optimized Overlapping Package Page Paging Panacea parallel part Partial Partition partitioned Partitioning PASS Performance PFS plan Plans Practices problem Problems Procedure Program programmatically Programming Protection Queries query read recent Recursive Related released Reports Restore return ROLLUP ROWCOUNT Runtime Security Select Sequence sequential Server Services set SETS Shooting shorts sizes Solutions Sortable SPARSE Spool SQL SQLIOSim SSIS Stalled Star Statement Statements stats Stored strategy Stuck Studio Submission Subreports Suggested Summarizing system Table Tables Tampa Task Than there through Timeouts Total Traces Transaction transfer Tricks Trouble TSQL turning understand Understanding undocumented Unique unused upgrade Upgrading Useful Value variables VDI Vista Will Windows Wireless
Microsoft licensed the Dundas guage control for SQL Server 2008. I believe they did the same the chart control. I have been playing around with the gauge control for a new project I am working on tonight and I thought I would show few pics because they are pretty cool.
Here is it displaying real time perfmon data:
Here are several more but not all of the other other gauge types. Click to expand.
How cool is that? Ok, ok, it is a JeeYouEye but your boss will think you have been taking king fu lessons.
Reminder: You can catch me at the North Texas SQL Server User's Group tomorrow night. The Data Collection presentation that I put together may be short so I have a few backup slides and demo's on filtered indexes, queries hashes and the 2 second SQL Server 2008 upgrade. Directions and details can be found at http://northtexas.sqlpass.org
Hope to see you there.
I found this in google reader this morning. PowerGUI wins "Breakthrough Product of TechEd 2008". I have looked it before. I believe Brent O pointed me to it. Well, I missed the script editor before. This baby is nice. You can run commands directly from the editor. It has intellisense and a debugger! Oh yah, it is free and open source.
Read more and download here: http://powergui.org
To load the SQL 2008 RC0 snapin and format files, you have to run these commands.
Add-PSSnapin SqlServerProviderSnapin100Add-PSSnapin SqlServerCmdletSnapin100
Update-TypeData -PrependPath "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLProvider.Types.ps1xml"Update-FormatData -PrependPath "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLProvider.Format.ps1xml"
P.S. Be wary of the "cult of posh"
The new SSMS splash screen....
I believe this is a new feature RC0. If I missed it before, shame on me because this is awesome. My first 2 blog posts(Part 1 and Part 2) were on doing a similar things with query patterns. It is basically an additional column on sys.dm_exec_requests and sys.dm_exec_query_stats that hold a binary hash. The hash is same for queries that are the same or similar. For example, these queries should all have the same hash(disregard parameterization and trivial plans for simplicity):
select c1 from t1 where c2 = 1
select c1 from t1 where c2 = 2
But this query will have a different hash:
select c1 from t1 where c2 > 1
This allows you to group and aggregate similar queries by the hash. It is really useful in OLTP environments where the same query might only take milliseconds but execute 1000's of times a second. If you don't aggregate, those those types of queries may not show up on the radar but might be responsible 90% of the CPU usage. Shaving a couple of cycles of CPU off of one of those and server-wide utilization drops.
Here is a nice sample from BOL.
Â
1:SELECT TOP 5 query_stats.query_hash AS "Query Hash",
2:SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
3:MIN(query_stats.statement_text) AS "Statement Text"
4:FROM
5: (SELECT QS.*,
6:SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
7: ((CASE statement_end_offset
8:WHEN -1 THEN DATALENGTH(st.text)
9:ELSE QS.statement_end_offset END
10: - QS.statement_start_offset)/2) + 1) AS statement_text
11:FROM sys.dm_exec_query_stats AS QS
12:CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
13:GROUP BY query_stats.query_hash
14:ORDER BY 2 DESC;
15:GO
The big news of the day is the public release of SQL Server 2008 RC0. I would like to point out that the samples have been updated on codeplex for RC0 and the feature pack is available as a separate download.
I am arm wrestling with VPC and Win2008. I may switch over to vmware or Win2003 :(. Hopefully, I will have RC0 up and running tonight. I hope SQL2008 RC0 followed by VS2008 + sp1 beta is the way to go... I am going to take a backup of the vhd before installing VS2008.
Edit: You can also download BOL separately.
Also in today's new, SQL Server 2008 stamps are available at your local post office? William Green provides a pic from the field.
An unconfirmed rumors of a public SQL Server 2008 RC0 and Windows 2008 VHD's are coming in but we think that is just right wing rhetoric. While the two products are certainly made for each other, the high oil prices and credit crunch make this combination unlikely until after the democratic convention in August.
Reporting from Dallas, I am Harry Q. Butte.
Some of the MSFT employees at TechED told me that SQL Server 2008 RC0 will hit next week. I suspect Tuesday so it can be announced in TechEd ITPro keynote. That is all.
Buck Woody spoke of features not currently in the FEB CTP. A reader pointed this out to him and he responded in this post. So we can infer two things: RC0 is going to pop any time now. There are going to be new features.
I don't expect there to be any huge new features. Anyone have any rumors? I would also guess that they will have an RC1 in late July\early August. If all goes well, RTM in September. This is all speculation, of course. They may go straight to RTM if RC0 is solid. To be honest, FEB CTP has been pretty solid for me. I have not put it under load but I have tried some pretty crazy things with it.
Click here to see the existing public new features in SQL Server 2008: http://www.microsoft.com/sqlserver/2008/en/us/overview.aspx