Ever so often, queries include some aggregates data. In a datawarehouse environment, they are frequently used for metric and Key Performance Indicator (KPI) calculations. Aggregates are also used a lot for reporting purposes and for statistical computation. When a query perform poorly, our first instinct is to add an index to speed it up. Creating a […]
Posts Tagged ‘query plan’
March 25, 2012 by Francois · 3 Comments
Filed under: Performance, TSQL
Social Tagging: Index • indexed view • join • query plan • TSQL • view
Filed under: Performance, TSQL
Social Tagging: Index • indexed view • join • query plan • TSQL • view
October 23, 2011 by Francois · 3 Comments
Filed under: Administration, Performance
Social Tagging: collation • distributed query • linked server • query plan • remote query • sql server • statistics • sub-optimal query plan • TSQL • Use Remote Collation
Filed under: Administration, Performance
Social Tagging: collation • distributed query • linked server • query plan • remote query • sql server • statistics • sub-optimal query plan • TSQL • Use Remote Collation
Linked servers allows to issue SQL commands against OLE DB providers. Because Microsoft also makes available an “OLE DB Provider for ODBC Drivers”, it is also possible to issue queries against a variety of ODBC drivers. With linked servers and distributed queries, you can query all sorts of data sources and merge them on the […]
September 13, 2011 by Francois · 2 Comments
Filed under: Performance, TSQL
Social Tagging: Index • indexes • OPTION (RECOMPILE) • query plan • set statistics profile • statistics • sub-optimal query plan • table variables • temp tables
Filed under: Performance, TSQL
Social Tagging: Index • indexes • OPTION (RECOMPILE) • query plan • set statistics profile • statistics • sub-optimal query plan • table variables • temp tables
The main reason why Microsoft introduced table variable in SQL Server 2000 is to reduce stored procedure recompilations (a recompilation occurs when the stored procedure execution plan is recreated). Table variables also have a well defined scope (the current procedure or function) and they induce less logging and locking (because their transaction last for a […]
July 15, 2011 by Francois · 1 Comment
Filed under: Performance
Social Tagging: local variable • OPTION (RECOMPILE) • plan cache • query plan • set statistics profile • statistics • sub-optimal query plan • TSQL • WITH RECOMPILE
Filed under: Performance
Social Tagging: local variable • OPTION (RECOMPILE) • plan cache • query plan • set statistics profile • statistics • sub-optimal query plan • TSQL • WITH RECOMPILE
A query plan is a set of steps generated by the database engine to retrieve data. Query plans are produced by the query optimizer from SQL statements. SQL Server automatically caches query plans and try to reuse them whenever possible. For many applications (such as OLTP transactional applications), plan reuse is a very good thing since […]
RSS