This March, I’ll be presenting a 2-day seminar as part of AddSkills Expertseminarium series in Stockholm on “Performance and new developer features in SQL Server 2014”. I’ll start with techniques to measure and monitor query performance in general, as well as troubleshooting query performance problems. But from there, I’ll cover the features in SQL Server […]
Bob Beauchemin
SQL Server Blog
Expertseminarium in Stockholm on performance and SQL Server 2014
Posted on: January 29, 2014 11:56 pm
Integrating SQL Server 2014 constructs into the T-SQL canon
Posted on: September 4, 2013 2:59 pm
So I was going through the features in SQL Server 2014, seeing how and where they fit into the canon of SQL Server’s T-SQL. I’m going to add some of this for my advanced T-SQL Immersion Event at the end of September. SQL Server 2014 is supposed to be a release with no T-SQL changes at all. […]
Tracing The New Cardinality Estimation in SQL Server 2014
Posted on: August 4, 2013 7:01 pm
According to the whitepaper “Mission-Critical Performance and Scale with SQL Server and Windows Server” there’s a new cardinality estimator in SQL Server 2014. The whitepaper doesn’t say a lot about how it works, but does mention “trace information will be provided”. That’s XEvent trace of course, and the trace event is “query_optimizer_estimate_cardinality”. I decided to […]
Does everybody get that? (Spatial Index Reprise)
Posted on: June 21, 2012 11:43 am
This post is an example of the "bring your own problem" approach in the classroom, inspired by upcoming SQLskills Immersion Events in August. See previous posting for the reason behind the blog post title. I believe it was John Lennon who wrote: "You say you got a real solution. Well, you know, We'd all love […]
Speaking at Portland SQL Server User Group this Thursday
Posted on: December 5, 2011 1:43 pm
It's gotten around to the week of the Portland, Oregon, SQL Server User Group meeting. And there's not only going to be a holiday dinner, but I've volenteered to speak again. This time, it's about parameterization and autoparameterization on the server and with clients, sure to create more than a little discussion. Join us for […]
Speaking at Portland, OR, User Group in December
Posted on: November 4, 2011 1:54 pm
Speaking at a user group is a bit different from speaking at a conference or other event. Because you know the folks there personally (hi Ken, Chris, and all ;-), you can ask around and "do requests", rather than just do what's interesting to you at the time. This one's a request. On December 8, I'll […]
LOTS of New Goodies in SQL Server Denali Query Plans
Posted on: September 27, 2011 10:48 pm
After the last post on new warnings in SQL Server Denali Showplan, just for the fun of it, I had a look at the XML version of the showplan. And saw some fairly amazing new things I hadn't noticed before. Everyone's been looking into the new "Batch Execution Mode" with the new ColumnStore index feature. […]
Additional Warnings in SQL Server Denali Query Plans
Posted on: September 27, 2011 9:31 pm
Sort and hash warnings can be found using SQL Server profiler, but they don't appear in query plans like "Columns With No Statstics" warnings in SQL Server 2008 R2 and below. In SQL Server Denali, they DO appear in query plans. No profiler needed. I'm not sure if all the pertainant events from the trace […]
Spring 2010 SQLConnections
Posted on: March 31, 2010 2:23 pm
In just over a week, I'm headed out to Las Vegas for SQLConnections. I'm really looking forward to presenting on the topics I'm doing there, but especially excited about the response to my post-conference workshop on writing data access code and stored procedures for best performance. No one can do more to affect the speed […]
Latest column on data access and perf available
Posted on: July 28, 2009 11:40 am
My latest MSDN column is available in the August issue and called "How Data Access Affects Database Performance". It was inspired by a problem that one of my DBA friends noticed in the database that could only be fixed by changing the data access code. I noticed that there were two articles in the issue […]
Spatial Indexes and ANSI JOIN – ON syntax
Posted on: July 20, 2009 1:51 pm
During a talk about spatial indexes and performance last week, I was surprised by a question about using ANSI-92 JOIN – ON syntax in spatial queries. I'd coded: SELECT * FROM geonames.dbo.geonames g JOIN Sample_USA.dbo.Counties c ON g.GEOG.STIntersects(c.geog) = 1 WHERE c.ID = 1569 Later I learned that the question was prompted by the […]
How to ensure your spatial index is being used
Posted on: April 25, 2009 10:02 pm
I've answered quite a few questions lately about how to make sure that your spatial index is being used. Use of a spatial index with a large or complex spatial dataset can mean the difference between a query that takes minutes to execute and subsecond execution. You also want to use an index hint as […]
SQL Server 2008 and TokenAndPermUserStore
Posted on: September 19, 2008 8:08 pm
In the past year or so, a few installations had begun to experience problems with the SQL Server 2005 security cache (aka TokenAndPermUserStore) growing too large over time. Some manifestations are connection and query timeouts and queries that take a long time. The folks at PSS published the canonical blog entry about this problem, including […]
Performance features in SQL Server 2008 RC0 – Hashes for queries and query plans
Posted on: June 9, 2008 9:02 pm
There's a newer version of SQL Server 2008 (Release Candidate 0) available on MSDN and Technet. Another performance-related feature (actually its additional information) allows you to have better visibility into performance-affecting queries. This information is available as columns query_hash and query_plan_hash in the sys.dm_exec_query_stats and sys.dm_exec_requests DMV. There's a really nice illustrative example in Books […]
Performance features in SQL Server 2008 RC0 – Optimize for Adhoc Workloads
Posted on: June 9, 2008 8:40 pm
There's a newer version of SQL Server 2008 (Release Candidate 0) available on MSDN and Technet. There's a couple of features that show up in RC0 that are performance related. One is a server setting 'optimize for ad hoc workloads'. This one tells SQL Server to save a "compiled plan stub" in the query cache […]
Saving a roundtrip when inserting rows with filstream columns
Posted on: May 30, 2008 6:29 pm
It's a good idea when talking to a database to save on network roundtrips. The table-valued parameter in SQL Server 2008 is an example of a feature that can reduce them in the "1 order, 1-n detail items" use case. So its always been mildly irritating that in order to insert a row with a […]
MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 2
Posted on: February 14, 2008 11:55 pm
This post covers LINQ to SQL and EF worry #2. That is: LINQ to SQL and EF will encourage "SELECT * FROM…" style coding because you get back a nice, clean object this way instead of the less-useful anonymous type you receive by doing projection. This will also make most/all covering indexes useless. LINQ to […]
MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 1
Posted on: February 14, 2008 1:47 am
I'd been meaning to write this entry for a while, about my opinion on LINQ to SQL and the ADO.NET Entity Framework and performance. I've just finished reading the 2-part blog series "Exploring the Performance of the ADO.NET Entity Framework", and was surprised (I guess) that the database performance aspect was barely mentioned. One way to […]
More hints available to plan guides in SQL Server 2008
Posted on: December 28, 2007 6:55 am
To round out the new plan guide-related features in SQL Server 2008, there is a new way to express a table hint that increases the plan guide's reach. The sp_create_plan_guide procedure requires a hint as the last parameter. This can be in a form OPTION (hint), just an XML query plan (in SQL Server 2008 […]
Plan freezing and other plan guide enhancements in SQL Server 2008
Posted on: December 27, 2007 10:26 pm
One of the nifty new SQL Server 2005 features was called "plan forcing". You could acheive plan forcing by using the USE PLAN query hint but this made such a hideous query that the better way to do this is to use a plan_guide. A plan_guide is a named database object (like table or view) […]
Some questions and answers on plan guides
Posted on: December 30, 2006 6:23 am
Thought I'd answer one last question or two from the last time I did the talk on SQL Server 2005 plan guides. If you're asking at this point "what is a plan guide", reference the SQL Server 2005 BOL or this blog entry. Q. Can you use a plan guide on an encrypted procedure? I want to put […]
Another behavior that follows schemas, query plan reuse
Posted on: October 28, 2006 5:55 pm
When I discuss separation of users and schemas in SQL Server 2005, I usually think of ownership chains (that follow the object owner which is always the schema owner unless you change it) or object resolution (which now goes: special name, then default_schema/procedure schema, then DBO). Another item that now follows schemas rather than users […]
Federation Enhancements/Changes in SQL Server 2005
Posted on: June 27, 2006 12:50 am
SQL Server 2005 implements quite a few technology pieces to help database developers and administrators acheive scale-out while acheiving the appearance of (or actually accomplishing) application or server federation. The information is scattered throughout the books online, but you can read a nice whitepaper by Roger Wolter on how to choose among the features here. I'll be […]
Move over developers! SQL Server XQuery is actually a DBA tool
Posted on: March 3, 2006 8:38 am
While teaching SQL Server 2005 to developers, I'd always point out the irony in the fact that the new EVENTDATA() function, used to return information in a DDL trigger or EVENT NOTIFICATION, returns XML and would be used almost exclusively by DBAs. This seems almost like a nefarious plot to force DBAs, who would be […]
I hate to wait (doesn’t everyone)
Posted on: January 12, 2006 10:53 pm
I don't know anyone that likes to wait for an application to respond. This is especially a concern with web applications; you start to wonder if the web infrastructure has a problem. Should you hit by "Order" button again? Because waiting is subjective it's nice to be able to know, when a user calls, how […]
Query plan guides in the SQL Server 2005 BOL
Posted on: February 17, 2005 6:20 am
Just catching up on my blogging before a little vacation next week. Browsing through the SQL Server BOL from the December CTP, I came across some information on something called "plan guides". There is info on some stored procedures that create and manage plan guides, a database option (in ALTER DATABASE) and a system view […]
