We have a SQL Server DB with 150-200 stored procs, all of which produce a viewable query plan in sys.dm_exec_query_plan except for one. According to http://msdn.microsoft.com/en-us/library/ms189747.aspx: Under the following conditions, no Showplan output is returned in the query_plan column of the returned table for sys.dm_exec_quer...
I have a very simple query that's giving me unexpected results. Hints on where to troubleshoot it would be welcome. Simplified, the query is: SELECT Obs.obsDate, Obs.obsValue, ObsHead.name FROM ml.Obs Obs JOIN ml.ObsHead ObsHead ON ObsHead.hdId = Obs.hdId WHERE obs.hdId IN (53, 54) This gives me a query cost of:...
I'd like to be able to convert SQL Server query plans from XML to images. Ideally a vector format, but a bitmap would do. Is there an open source library to do this? Or can I use one of the SQL Server Management Studio DLLs? Thanks. ...
I'm trying to understand physical operators in SQL Server execution plans. This page is quite helpful: http://technet.microsoft.com/en-us/library/ms191158.aspx SSMS 2008 shows some physical operator properties that are not displayed in SSMS 2005: Estimated Number of Executions and Number of Executions. But what do these actually mean, ...
In another question I was trying to research how to control SQL Server's query plan caches: http://stackoverflow.com/questions/2593749/is-there-an-equivalent-of-optionrecompile-or-with-recompile-for-an-entire-c ...and I found trace flag 253 via this article: http://www.sqlservercentral.com/Forums/Topic837613-146-1.aspx The article is...
Question: Does passing DateTime.Now as a parameter to a proc prevent SQL Server from caching the query plan? If so, then is the web app missing out on huge performance gains? Possible Solution: I thought DateTime.Today.AddDays(1) would be a possible solution. It would pass the same end-date to the sql proc (per day). And the user wou...
I'm trying to optimise a PostgreSQL 8.4 query. After greatly simplifying the original query, trying to figure out what's making it choose a bad query plan, I got to the point where running the query under EXPLAIN ANALYZE takes only 0.5s, while running it normally takes 2.8s. It seems obvious then, that what EXPLAIN ANALYZE is showing me ...
Here's a table: CREATE TABLE Meetings ( ID int PRIMRY KEY IDENTITY(1,1) StartDate DateTime NOT NULL, EndDate DateTime NULL, Field1 varchar(50), Field2 varchar(50), Field3 varchar(50), Field4 varchar(50) ) There's several thousand rows. The data ranges can be varying sizes (from a couple days up to 50 years). Here's a q...
In one of my queries there's an insert of data into a temp table. Looking at the query plan, it shows the the actual insert into temp table took 54% (just inserting data into temp table). However, no rows are being inserted into the temp table. Why does the plan show a non zero value when no rows are being inserted? ...
It's difficult to explain, but I'll try. As you see on query_plan picture attached (It is query plan for "All in one place" query described below), there are 3 almost the same "blocks" - my question is WHY? It seems to me that when I have "all in one" (see below) query the "Init" block (that is rather heavy) is run three times with di...
To perform transformations in my database, I frequently use a chained set of views. Within the views will be common table expressions. For example I would have the following: CREATE VIEW TransformationStep1 AS WITH Transformation1A AS ( SELECT Field1, Field2, Field3, Bla(Field1) AS Calc FROM Table1 ), Transformation1...