Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

SQL Server : Pourquoi ma requête est lente en production, mais pas quand je l’e

SQL Server : Pourquoi ma requête est lente en production, mais pas quand je l’exécute dans Management Studio ? Derrière cette question se cache un des plus gros problèmes d'optimisation de requêtes. Il faut savoir que le moteur de base de données est adaptif et qu'il sait s'adapter aux ressources disponibles et aux volumes de données présent dans les tables. C'est d'ailleurs bénéfique dans la majorité des cas… Qui souhaiterait utiliser 8 processeurs pour exécuter une requête sur un serveur utilisant déjà 100% des CPU ? Le problème du phénomène d'écrit plus haut n'est généralement pas là. En effet même l'activité du serveur est calme et que la même requête avec les mêmes paramètres est exécutée avec quelques secondes d'intervalle entre votre application et Management Studio le comportement change. En fait une application ado.net et Management Studio ne partage pas les même paramètres de connexion ce qui explique l'origine de ce problème. On retrouve ces fameux paramètres de connexion avec : select * from sys.dm_exec_sessions Les colonnes (entre autres) :  quoted_identifier  arithabort  ansi_null_dflt_on  ansi_defaults  ansi_warnings  ansi_padding  ... Sont considérées comme telles… SQL Server va alors mémoriser certains de ces paramètres avec le plan d'exécution de la requête. De fait, le plan ne sera réutilisé que si les paramètres sont les mêmes. La requête suivante sera déclinée en 2 plans d'exécutions différents : SET ANSI_NULLS ON SELECT * FROM MaTable WHERE MaCol = NULL SET ANSI_NULLS OFF SELECT * FROM MaTable WHERE MaCol = NULL Ici le problème sera visible si vous avez une grande quantité d'enregistrement ayant NULL stockée dans la colonne MaCol et un index présent sur cette même colonne. Les plans seront alors très différent l'un de l'autre. La problématique est double. D'une part les paramètres de connexion sont différents entre les 2 applications (Ado.Net et Management Studio) et d'autre part la requête de part sa complexité peut être déclinée en plusieurs plan d'exécution différent. La solution à ce problème sera supprimée le plan fautif (celui qui est le plus lent des 2), en vidant par exemple le cache de procédure (DBCC FREEPROCACHE) mais cette solution n'est que temporaire, le problème reviendra tôt ou tard. La solution durable est de signaler à SQL Server que la requête sur lequel nous planchons risque de générer des plans d'exécution différent en fonction du contexte et de tout simplement lui sire de régénérer un nouveau plan à chaque exécution : SELECT * FROM MaTable WHERE MaCol = NULL OPTION ( RECOMPILE ) Ou dans le cas d'une procédure stockée on ajoutera WITH RECOMPILE à sa définition. Bonne résolution... Troubleshooting Slow Servers Missing indexes, an inadequate storage I/O subsystem, or a slow network are only some of the possible reasons why a SQL Server database engine might slow down, which is why finding the true cause of a performance bottleneck is vital. Resolving these problems might seem difficult, but if you have the right tools available, they can be easy enough to fix so that performance will improve. Denny Cherry | Apr 21, 2012 When troubleshooting a performance problem within a SQL Server database instance, there are a variety of places that you can look to determine its root cause. Some of the biggest performance bottlenecks usually boil down to several causes:  Missing indexes  Poor index design  Poorly designed database schema  Inadequate storage I/O subsystem  Buffer pool too small  Slow network  Wrong technology used Resolving these problems might seem difficult, but if you have the right tools available, they can be easy enough to fix so that performance will improve. Missing Indexes Disk performance problems and an increased number of deadlocks are some of the potential indicators that you might have indexes missing. One way to easily determine if there are missing indexes is to use two dynamic management views (DMVs): sys.dm_db_missing_index_details and sys.dm_db_missing_index_columns. Between these two DMVs, you can easily identify many of the indexes that need to be created to improve performance. However, there's a catch: The system doesn't understand that indexes already in existence might only need another column added to their included columns list. So, SQL Server might recommend that another entire index be created when all that's needed is a new column in the existing index. Because of this, you shouldn't just take the information from the DMVs and create all the specified indexes. The reported indexes should be compared against the indexes that already exist in the database to ensure that duplicate indexes aren't being created. Duplicate indexes can lead to additional writes, updates, and deletes, causing performance problems. A better approach is to use a query like that in Listing 1 to identify the indexes that are missing from the database on which the query is being run. This query uses the db_id() system function to restrict the output to that from the current database. So, to run it in your database, simply specify that database in the Available Databases drop-down box in SQL Server Management Studio (SSMS) or add a USE statement before the query. SELECT a.avg_user_impact * a.avg_total_user_cost * a.user_seeks, db_name(c.database_id), OBJECT_NAME(c.object_id, c.database_id), c.equality_columns, c.inequality_columns, c.included_columns, c.statement, 'USE [' + DB_NAME(c.database_id) + ']; CREATE INDEX mrdenny_' + replace(replace(replace(replace (ISNULL(equality_columns, '') + ISNULL(c.inequality_columns, ''), ', ', '_'), '[', ''), ']', ''), ' ', '') + ' ON [' + schema_name(d.schema_id) + '] .[' + OBJECT_NAME(c.object_id, c.database_id) + '] (' + ISNULL(equality_columns, '') + CASE WHEN c.equality_columns IS NOT NULL AND c.inequality_columns IS NOT NULL THEN ', ' ELSE '' END + ISNULL(c.inequality_columns, '') + ') ' + CASE WHEN included_columns IS NOT NULL THEN 'INCLUDE (' + included_columns + ')' ELSE '' END + ' WITH (FILLFACTOR=70, ONLINE=ON)' FROM sys.dm_db_missing_index_group_stats a JOIN sys.dm_db_missing_index_groups b ON a.group_handle = b.index_group_handle JOIN sys.dm_db_missing_index_details c ON b.index_handle = c.index_handle JOIN sys.objects d ON c.object_id = d.object_id WHERE c.database_id = db_id() ORDER BY DB_NAME(c.database_id), ISNULL(equality_columns, '') + ISNULL(c.inequality_columns, ''), a.avg_user_impact * a.avg_total_user_cost * a.user_seeks DESC When reviewing the output from the query in Listing 1, look for queries that have a high value in the first column. The higher the number, the more savings that will be seen by adding an index. Poor Index Design Poor index designs for database tables will usually manifest as slow running queries and queries that have a high execution plan cost. The easiest way to identity these problems is to query the procedure cache because queries' execution plans remain in the cache as long as there is enough memory to store them. Listing 2 shows a sample query that looks for queries with a high total cost. When reviewing the output from Listing 2, look at the StatementSubTreeCost column. The higher the number, the more expensive the execution plan. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT query_plan AS CompleteQueryPlan, n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost, dm_ecp.usecounts FROM sys.dm_exec_cached_plans AS dm_ecp CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS dm_eqp CROSS APPLY query_plan.nodes ('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qp(n) ORDER BY n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') DESC GO In SQL Server 2012, you can set up an Extended Events session to capture the execution plans for queries. However, it isn't recommended that Extended Events be used to capture execution plans in high-load systems as doing so could overload the system by 21% or more by capturing the showplan_xml column. For more information on how to properly design indexes, see SQL Server 2008 Query Performance Tuning Distilled (APress, 2009) by Grant Fritchey and Sajal Dam. Another book you might want to check out is Expert Performance Indexing for SQL Server 2012 ( APress) by Jason Strate and Ted Krueger, which is scheduled to be released in May 2012. Poorly Designed Database Schema One of the biggest killers of database performance is a poorly designed database schema. Evidence of a poorly designed database schema can be seen when lots of I/O requests occur while queries are being run. The easiest way to look for this evidence is to use SQL Server Profiler to capture the queries that have the highest I/O load, then run those queries in SSMS with SET STATISTICS IO enabled. Between the table I/O outputs and the execution plans, you'll get a good indication of which table is having a performance problem. Fixing Schema Problem After the problematic table has been identified, fixing its schema problem is the next step. Sadly, fixing the problem usually isn't as simple as finding the problem. The table might need to be normalized or denormalized, depending on the table's design. Or perhaps the table needs to be vertically partitioned because there's too much data in it. Conversely, maybe the vertical partitioning was taken a little too far. In case you're unfamiliar with vertical partitioning, it's a technique in which a single table is broken into multiple tables, often using a date as the breakpoint. For example, instead of putting the sales history for every year into a single table, you could put the sales history for each year into a separate table. Inadequate Storage I/O Subsystem The most obvious way to uploads/Ingenierie_Lourd/ lenteur-sql-server.pdf