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
New in SQL Server 2008 is a server setting called “Optimize for adhoc workloads”. I was happy to see this. You should be too especially if you have ever had arm wrestle an app that causes a bloated proc cache on an x86 box. Ugghh… Adam blogged on it here, Bob blogged on it here and here is the documentation.
I was wondering how this setting would play with the forced parameterization database setting. It looks like forced parameterization trumps the new server wide setting. If my simple tests below are right, it could present an interesting problem. What if your app generates a lot of totally unique adhoc queries that have no benefit from parameterization and you have queries that would benefit from parameterization? Well, the answer is still the same. Write stored procedures. :)
In the real world, sometimes you get a pig and all you can do is put lipstick on it. If you have to choose, “Optimize for Adhoc” would most benefit memory\IO bound servers while “forced parameterization” could help both the CPU and memory usage. However, that is a very general statement. Other things should be considered so it will depend.
--So what happens when you turn on "Optimize for adhoc" and forced parameterization exec sp_configure 'show advanced options', 1reconfigure with overridegoexec sp_configure 'optimize', 1reconfigure with override alter database master set parameterization forceddbcc freeproccache --Let's run a query will not generate a trivial plan.select name, object_id, create_datefrom sys.all_objectswhere object_id = 3 and create_date = '2008-07-09 16:19:59.943'go --The CacheObjType is a compiled plan and 57344 bytesselect p.cacheobjtype, p.size_in_bytes , s.*from sys.dm_exec_query_stats sjoin sys.dm_exec_cached_plans pon s.plan_handle = p.plan_handlecross apply sys.dm_exec_sql_text(sql_handle) where text like '%select name , object_id , create_date from%' --Now, let’s turn off forced parameterizationalter database master set parameterization simpledbcc freeproccache select name, object_id, create_datefrom sys.all_objectswhere object_id = 3 and create_date = '2008-07-09 16:19:59.943'go --The CacheObjType is a Compiled Plan Stub and 320 bytesselect p.cacheobjtype, p.size_in_bytes , s.*from sys.dm_exec_query_stats sjoin sys.dm_exec_cached_plans pon s.plan_handle = p.plan_handlecross apply sys.dm_exec_sql_text(sql_handle) where text like '%select name , object_id , create_date from%'
--So what happens when you turn on "Optimize for adhoc" and forced parameterization exec sp_configure 'show advanced options', 1reconfigure with overridegoexec sp_configure 'optimize', 1reconfigure with override alter database master set parameterization forceddbcc freeproccache
--Let's run a query will not generate a trivial plan.select name, object_id, create_datefrom sys.all_objectswhere object_id = 3 and create_date = '2008-07-09 16:19:59.943'go
--The CacheObjType is a compiled plan and 57344 bytesselect p.cacheobjtype, p.size_in_bytes , s.*from sys.dm_exec_query_stats sjoin sys.dm_exec_cached_plans pon s.plan_handle = p.plan_handlecross apply sys.dm_exec_sql_text(sql_handle) where text like '%select name , object_id , create_date from%'
--Now, let’s turn off forced parameterization
alter database master set parameterization simpledbcc freeproccache
select name, object_id, create_datefrom sys.all_objectswhere object_id = 3 and create_date = '2008-07-09 16:19:59.943'go
--The CacheObjType is a Compiled Plan Stub and 320 bytesselect p.cacheobjtype, p.size_in_bytes , s.*from sys.dm_exec_query_stats sjoin sys.dm_exec_cached_plans pon s.plan_handle = p.plan_handlecross apply sys.dm_exec_sql_text(sql_handle) where text like '%select name , object_id , create_date from%'
I am pumped up about TechEd. Bill Gates's last technical keynote. He has to have a bomb to drop... The launch of Windows\VS\SQL Server 2008. All of the speakers are reaching deep into their bag of tricks and bringing new hardcore sessions. The universal party. The blogger's lounge. Twitter, FriendFeed, MSDN, and Technet are all buzzing with posts.
I found out a couple of days ago that a speaker would not be able to make it due to a family emergency. I offered to present his session on DMV's so I will be presenting these sessions:
Using Dynamic Management Views to Improve Your Development
Dynamic Management Views were added to SQL Server 2005 and have been enhanced in SQL Server 2008. While they provide great functionality and usefulness, it appears they are not widely understood or implemented as yet. This session provides an overview of the Dynamic Management Objects available (both Views and Functions) and describes those considered the most useful. It shows how they can be utilized in monitoring, troubleshooting, and ongoing management of SQL Server systems and in gaining an understanding of SQL Server’s operation. The session also illustrates how they can be used to improve the database development experience and how they can be used in custom reports within SQL Server Management Studio.
Database Platform300BRKJason Massie
Windows Management Instrumentation (WMI) for the Command Line DBA
This is a demo filled session on how a command line DBA can unleash the power of WMI. WMI allows the DBA access to information that was previously hard to get or unavailable like the application event logs, OS perfmon counters, and OS events. This session covers WQL queries from SSIS, accessing OS performance counters from SQL, automated reactions to WMI events, WMI from Windows PowerShell and much more.
Database Platform400BRKJason Massie
Troubleshooting Query Plans Gone Wrong
Have you ever flushed the procedure cache to resolve a performance problem? Have you ever had to use a join or index hint when you should not have to? Have you ever updated statistics to fix a performance problem and wondered why it worked? In this session, we get deep into how the query optimizer decides how to execute a SQL statement. We look at common problems that cause the optimizer to choose the wrong access paths like underestimation. We go over a detailed demo on parameter sniffing, problems with local variables and statistics. We also look at solutions to these problems in several areas including good design at the application, schema, and query levels, proactive maintenance and reactive fixes. This session applies to Microsoft SQL Server 2000 through 2008 but we pay special attention to new features in SQL Server 2008 like plan freezing, new hints, filter indexes, and statistics among others.
I'll be there Monday through Saturday. Hope to see you there!
There are a couple new features that I would like to quickly point out. These are basically just tools that I use daily so I integrated them into the site. I hope you find them useful.
ALT HEAD: HOWTO: Inserting dummy data
Kalen Delaney's first DVD on SQL Server internals is available for viewing online. More details here.
Connor Cunningham, formerly on the SQL query optimizer team, is now blogging at SQL Skills and is whipping out posts on optimizer related topics at a frantic pace. Check out the latest here or all of them here.
Dan Jones rebuffs the idea that SQL Server 2008 is a "dot release".
CTP6 may or may not be coming soon. Chris says that CTP5 will be given away at the HHH launch. This is in contrast to the "feature complete" CTP that was mentioned in the roadmap but who know...
Mario B has raised some eyebrows with his post on scheduler troubleshooting and SOS_SCHEDULER_YIELD waits and the possibility that is is not CPU pressure always. Linchi posted about it here and then SQLServerPedia posts shortly afterwards. Anyway, check out Mario's tools.