Устранение неполадок производительности Ad-Hoc запросов в SQL Server

Переводы статьи Переводы статьи
Код статьи: 243588 - Vizualiza?i produsele pentru care se aplic? acest articol.
Развернуть все | Свернуть все

В этой статье

Аннотация

В данной статье описывается устранение неполадок при низкой производительности многих одновременных нерегламентированных запросов в Microsoft SQL Server. В случае не определения точного источника неполадок обратитесь к следующей статье Microsoft Knowledge Base перед продолжения:
224587Устранение неполадок производительности приложения с помощью SQL Server

В данной статье предполагается, что 224587 КБ, используемого для сужения области проблемы и записи в журнал системного монитора Windows NT и приложения SQL Profiler, подробности трассировки определенных счетчиков, события и данные столбцов.

Характеристики проблем производительности

Проблема с производительностью имеет следующие характеристики:
  • Короткий нерегламентированных запросов, которые обычно имеют очень короткое время привести к низкой общей производительности системы при большое количество одновременно работающих пользователей запускать запросы.
  • Очень высокая или 100 процентов ЦП.
  • Нет связанных блокировки в периоды низкой производительности.

    Можно быстро найти блокировку, проверкаBLKстолбец в выводsp_whoсистемные хранимые процедуры. ЕслиBLKстолбец не имеет нулевое значение для числа системного процесса (SPID) идентификаторы, блокируется.
  • В некоторых случаях подчеркнул памяти сервера, а возможно появление ошибок, которые похожи на следующие ошибки:
    Ошибка: 701, уровень опасности: 17, состояние: 1
    Не хватает памяти для выполнения данного запроса.
    -ИЛИ-
    Сообщений 8645, уровень 17, состояние 1, процедура, строка 1
    Было превышено время ожидания ресурса памяти для выполнения запроса. Повторите запрос.

Улучшения в компиляции запроса

Из-за улучшений в архитектуре системы, начиная с SQL Server 7.0 специально оптимизатор запросов, можно заметить разницу в использовании системных ресурсов приложениями по сравнению с предыдущими версиями SQL Server. В частности SQL Server 7.0 может показать увеличение использования Процессора или памяти, но обычно являются более ранних версиях SQL Server на диске привязки ввода-ВЫВОДА. Эти изменения можно проследить до двух факторов:
  • Хэш и объединение соединений
  • Время компиляции запросов
Более ранние версии SQL Server полностью основывались на вложенных циклов итерации для выполнения объединения. Соединения вложенных циклов по своей природе использования дисковых операций ввода-ВЫВОДА. Начиная с SQL Server 7.0, хэширования и объединения соединения были введены. Хэш и объединение соединений сделать гораздо больше памяти чем соединение вложенными циклами обработки. Логический результат, Процессора и памяти выше при использовании этих методов соединения. Для получения дополнительных сведений об объединениях хэширования и слияния содержатся в разделах «Соединения общее представление О хеш-код» и «Сведения О слияния соединения» в документации по SQL Server 7.0.

Query compilation times are affected because the query optimizer has more options and information available than in earlier versions of SQL Server, including new hash and merge join techniques, improved search algorithms, and column statistics. This additional information permits the query optimizer to select the most efficient method to retrieve query data. However, the analysis and consideration of these new techniques and information requires processing time. This increased CPU usage may result in query compilation times that are longer than in earlier versions of SQL Server.

For most queries, this increase in compile time is offset by a decrease in execution time. The overall effect is that the query runs faster than in earlier versions of SQL Server. One exception, however, occurs with very small, simple, OLTP-type queries that have very low execution times. For these queries, the process of generating a query plan may have an equal or greater expense than the query execution. As a result, the query may perform slightly slower than in earlier versions of SQL Server. Because the difference is typically in milliseconds, these effects are not noticed for a particular query if it is executed individually. However, you may notice that overall system CPU usage is higher than in earlier versions of SQL Server if large numbers of ad-hoc queries are executed concurrently by a high number of users.

Develop parameterized queries

SQL Server 7.0 uses several new techniques, such as caching ad-hoc queries and automatic parameterization. However, the queries that SQL Server 7.0 automatically parameterizes are limited. Use the following methods to make sure that the query plans are parameterized and can be reused more effectively:
  • Parameter markersBoth the OLE DB and ODBC APIs permit parameters to be specified with a question mark when users submit queries. This can be very useful in any application, especially for middle-tier applications that have query generation modules where using stored procedures is not available. The query plan that is generated for queries that have parameter markers can be reused by any clients that execute the same query, even if different parameter values are specified. For more information, see the "Parameter Markers" topic in SQL Server 7.0 Books Online.
  • sp_executesqlнадписьюsp_executesqlstored procedure is called by the OLE DB provider or ODBC driver when parameter markers are used in an application. However, it may also be called directly by the application or in another stored procedure to explicitly parameterize ad-hoc queries. This can be very useful in applications or batch files where the EXECUTE statement is used to execute dynamic SQL statements. Unlikesp_executesql, the EXECUTE statement does not permit parameterization. This limits the chance of query plan reuse. For more information, see the "sp_executesql (T-SQL)" and "Using sp_executesql" topics in SQL Server 7.0 Books Online.
  • Stored proceduresStored procedures have many benefits, including the ability to parameterize queries and reuse execution plans. For more information, see the "Stored Procedures" and "Programming Stored Procedures" topics in SQL Server 7.0 Books Online.

View the Performance Monitor data

Use the Performance Monitor log to determine which system resources are causing the bottleneck. The Performance Monitor log can give you an overall picture of the system and help focus your attention when you view the SQL Profiler data. Review the Performance Monitor data from the time when performance was good through the time that performance decreased. Determine the counter that was affected first, and then determine which of the following issues is most relevant to your situation:
  • Object: Process
    Counter: Processor
    Instance: SQL Server
  • Object: Processor
    Counter: %Processor Time
    Instance: Check each processor instance
  • Object: SQL Server:Buffer Manager
    Counter: Free Buffers
  • Object: SQL Server:Buffer Manager
    Counter: Stolen Page Count
  • Object: SQL Server:Memory Manager
    Counter: Memory Grants Pending
  • Object: SQL Server:SQL Statistics
    Counter: SQL Compilations/sec
Если использование ЦП, компиляций SQL/с и свободных буферов счетчики являются высокий, ожидается предоставление памяти и число страниц, заимствованных счетчики находятся на низком уровне, это означает, что Процессор является узким местом. Сосредоточьтесь на том, как эффективно параметризовать и повторного использования планов запросов, чтобы избежать затрат на создание плана запросов и в разделе «Группирование трассировки приложения SQL Profiler классом событий» данной статьи. Если счетчики компиляций SQL/с и свободных буферов находятся на низком уровне, а число Заимствованных страниц и предоставление памяти в ожидается счетчики высоки, SQL Server не с ограниченным доступом к памяти. Сосредоточьтесь на поиск запросов, где используются хэш-соединений и может быть изменено на цикл соединения и содержатся в разделе «Группа трассировки приложения SQL Profiler, длительность» данной статьи. Для получения дополнительных сведений об этих счетчиков имя счетчика можно используйте для поиска документации по SQL Server 7.0.

Просмотр данных приложения SQL Profiler

При разрешении проблем с производительностью, очень полезно просмотреть данные приложения SQL Profiler. Нет необходимости просматривать все данные, которые можно захватить; процедуры будьте осторожны. Приложение SQL Profiler позволяет эффективно просматривать записанные данные. в менюСвойстваВкладка (наФайл:выберите пунктСвойства), Приложение SQL Profiler позволяет ограничить объем данных, отображаемого в удаления событий или столбцов данных, группирования или сортировки по столбцам данных и применения фильтров. You can search the whole trace or only a specific column for specific values (on theВ файлевыберите пунктНайти). You can also save the SQL Profiler data to a SQL Server table (on theФайл:menu, point toСохранить каки выберите командуTrace Table), and then run SQL queries against it.

Примечание.Make sure that you only filter a saved trace file. If you follow these steps on an active trace, you risk losing data that was captured since the trace was started. Save an active trace to a file or table first (on theФайл:выберите пунктСохранить как), and then reopen it (on theФайл:выберите пунктOPEN) before you continue. When you work with a saved trace file, the filtering does not permanently remove the data; the data is only hidden, not deleted. You can add and remove events and data columns to help focus your searches.

You should also focus on the areas where you receive the most benefit. The following factors can help increase application performance but not necessarily to the same degree. Before you implement any changes, determine how effective the changes may be depending on the following factors:
  • How frequently the query runs
  • How much improvement the query can be improved
For example, reducing the execution time of a single query from 1.5 seconds to 1.2 seconds may not be helpful if the query is not executed frequently throughout the day. However, if the query is executed very frequently by a high number of concurrent users, the performance improvement can be very effective. Conversely, improving a single query from 6 minutes to 3 seconds may not yield a noticeable increase in overall performance if it is rarely used. Use the grouping and filtering techniques in SQL Profiler and your knowledge of the application to estimate the effects of a particular query or procedure before you implement any changes. Focus on the most effective changes first, and then continue with iterations through other queries and procedures until you reach a level where performance has sufficiently improved.

After you save a SQL Profiler trace to a file or table, reopen the trace in SQL Profiler and review the contents. To group the SQL Profiler trace, follow these steps:
  • Group the SQL Profiler trace by duration:
    1. в менюФайл:выберите пунктСвойства.
    2. Перейдите на вкладкуData Columnstab, and then underGroupsзатем –UPto moveDuration. затем –DOWNto remove all other columns.
    3. Перейдите на вкладкуEventstab, and then remove all events exceptTSQL SQL:StmtCompletedиTSQL RPC:Completed. This permits you to focus on only the queries that are being executed.
    4. затем –Ok..
    Grouping by duration permits to easily see the SQL statements, batches, and procedures that are running the slowest. Review the trace when the problem is occurring, and create a baseline of good performance. You can filter by start time to break the trace into sections when performance is good and separate sections when performance is poor. Look for the queries with the longest duration when performance is good. These are most likely the root of the problem. When overall system performance decreases, even good queries can show long durations because they are waiting for system resources.

    Review the execution plans for the queries that most frequently have long durations. If you see that a hash join is being used, consider using the LOOP JOIN query hint to force a nested loop join for the query. If the execution time for the query using a loop join is less than, equal to, or even slightly higher than the execution time with the hash join, a loop join may be a better option if the computer is experiencing high memory and CPU usage. By reducing the stress on the resource bottleneck (CPU and memory), you can improve overall system performance. For more information about the LOOP JOIN query hint, see the "SELECT (T-SQL)" topic in SQL Server 7.0 Books Online.
  • Group the SQL Profiler trace by event class:
    1. в менюФайл:выберите пунктСвойства.
    2. Перейдите на вкладкуData Columnstab, and then under theGroupsheading, clickUPto moveEvent ClassиTextэтим кодом:Event Classon top. затем –DOWNto remove all other columns under theGroupsheading.
    3. Перейдите на вкладкуEventstab, and then make sure that all the events are included.
    4. затем –Ok..

Types of events

To see what types of events are occurring on the computer running SQL Server and how frequently the events occur, group by theEvent Classстолбец. Search this column for the following events:
  • MISC: Prepare SQL and Exec Prepared SQL; CURSORS: CursorprepareAPrepare SQLevent indicates that an SQL statement was prepared for use with a default result set (client-side cursor) using SQLPrepare/SQLExecute (for ODBC) or ICommandText::Prepare/ICommandText::Execute (for OLE DB) with the default cursor options: forward only, read only, rowset size = 1. ACursorprepareevent indicates that a server-side cursor was prepared on an SQL statement using SQLPrepare/SQLExecute (for ODBC) or ICommandText::Prepare/ICommandText::Execute (for OLE DB) with the one of the previous cursor options set to a non-default value. AnExec Prepared SQLevent indicates that either of the previous types of existing prepared statements was executed. If you see frequent occurrences of these events, your application is using the prepare/execute model when it opens result sets. If so, you must determine if you are using the prepare/execute model correctly.

    Ideally, an application prepares an SQL statement once and executes it many times so that the optimizer does not have to compile a new plan each time the statement is executed. Each time you run a prepared statement, you save the cost of the query compilation. If you only plan to execute a query one time, Microsoft recommends that you not prepare it. Preparing and then executing an SQL statement requires three network roundtrips: one to prepare the statement, one to execute the statement, and one to unprepare the statement. Preparing server-side cursors requires at least five round trips: one to prepare the cursor, one to execute or open it, one or more to fetch from it, one to close it, and one to unprepare it. Executing the query only requires one roundtrip.

    To see how effectively your application uses the prepare/execute model, compare the number of times these two events (prepare and execute) occur. The number ofExec Prepared SQLevents should be much larger than the total ofPrepare SQLиCursorPrepareevents (at least three to five times larger is a good estimate). This indicates that prepared statements are being reused frequently enough to overcome the increased overhead to create them. If the number ofPrepare SQLиCursorPrepareevents is roughly equivalent to the number ofExec Prepared SQLсобытия, возможно, приложение фактически не использует модель подготовки и выполнения. Попытка подготовить инструкцию один раз и повторно использовать его как можно больше. Можно также изменить приложение для подготовки инструкций один раз и повторно использовать эти инструкции.

    Приложение должно быть написано специально для эффективного использования модель подготовки и выполнения. Как долго вы закрывайте HSTMT ODBC или OLE DB объект ICommandText контролируются существования дескриптор подготовленной инструкции. Один общепринятой практикой является получение HSTMT, подготовить инструкцию SQL, подготовленные инструкции и затем освободить HSTMT, тем самым потери дескриптора плана подготовленного. Если этого не получают все преимущества от модель подготовки и выполнения. На самом деле наблюдается снижение производительности из-за дополнительные издержки, связанные с сетевым обращений. Приложение должно иметь метод HSTMT или объекта с дескриптором подготовленную инструкцию и получить к ним доступ для повторного использования. Поставщик или драйвер не выполняет это автоматически, приложение отвечает для реализации, обслуживания и с помощью этой информации. Если приложение не может сделать это, следует использовать маркеры параметров вместо метода подготовки и выполнения.
  • С помощью маркеров параметровПриложения могут использовать маркеры параметров для оптимизации использования одного и того же инструкции Transact-SQL несколько раз с различными входных и выходных значений. При первом выполнении запроса, он готов как параметризованный запрос и SQL Server создает и кэширует план параметризованного запроса. Последующие вызовы для одного и того же запроса с использованием одного и того же или разные параметры SQL Server не требуется создавать новый план запроса, SQL Server можно повторно использовать существующий план запроса, Замена текущих параметров.

    Если приложение использует маркеры параметров с помощью вызовов SQLExecDirect (для ODBC) или ICommandText::Execute (для OLE DB), драйвера или поставщика автоматически упаковывает инструкции SQL и выполняет его какпроцедуры sp_executesqlвызов.. Оператор не имеет для подготовки и выполнения отдельно. Когда SQL Server получает вызовпроцедуры sp_executesql, он автоматически проверяет кэш процедур для соответствующего плана и повторно использует план или создает новый план.

    Чтобы определить, если в данный момент в приложении используются маркеры параметров, можно выполнить поискTextстолбцов в трассировке приложения SQL Profiler для «sp_executesql.» Тем не менее посколькупроцедуры sp_executesqlможет быть вызван напрямую, не все экземпляры указать использование маркеров параметров.

    Для получения дополнительных сведений о модели подготовки и выполнения разделе "использовать выполнение плана кэширования и снова" в документации по SQL Server 7.0. Дополнительные сведения о маркерах параметров в разделе «Маркеры параметра» в документации по SQL Server 7.0.
  • SP:CompletedДинамических инструкций SQL, выполняемых с помощью инструкции EXECUTE команды отображаются какSP:Completedсобытие с текстом "Динамического SQL". Разверните узелSP:Completedсобытия, а затем поиск всех вхождений, которые имеют текст "динамического SQL". При наличии многих из этих событий, можно повысить производительность приложения с помощьюпроцедуры sp_executesqlвместо инструкции EXECUTE. надписьюпроцедуры sp_executesqlХранимая процедура позволяет SQL Server для повторного использования планов выполнения, если тот же запрос выполняется снова с помощью различных параметров. When you use the EXECUTE statement, the plan is not parameterized, and it is not reused unless the query is executed again using the same parameters.

    To determine the queries or procedures that use dynamic SQL events with the EXECUTE statement, note the Connection ID and Start Time of for each event. Ungroup the trace (removeEvent ClassиTextизGroupsheading). After you ungroup the trace, it is sorted in chronological order. You can filter the trace by Connection ID (on theFilterstab), and then remove all the event classes except theSP:StartingиSP:Completeevents for increased readability. You can then search for the Start Time of the event (on theВ файлевыберите пунктНайти). The results show when the dynamic SQL event started. If the event occurred in a stored procedure, the event appears between theSP:StartingиSP:Completedevents for that procedure. If the event did not occur in a stored procedure, it was executed as an ad-hoc query, and you can use the other data columns (Application Name,NT User Name, and others) to determine where the command was executed. To determine the text of the command and the context where it was executed, you can also add event classes, such asSQL:BatchCompletedиSQL:RPCCompleted.

    After you determine where the EXECUTE statement is being used, consider replacing it with a call tosp_executesql. For example, consider the following scenario where the EXECUTE command is used with dynamic SQL. A procedure takes a table name, ID, and idValue as input parameters, and then executes a SELECT statement from the table based on the ID value. Using an EXECUTE statement, the procedure looks similar to the following code:
    drop proc dynamicUsingEXECUTE
    		  go create proc dynamicUsingEXECUTE @table sysname, @idName varchar(10),
    		  @idValue varchar(10) as declare @query nvarchar(4000) -- Build query string
    		  with parameter. -- Notice the use of escape quotes. select @query = 'select *
    		  from ' + @table + ' where ' + @idName + ' = ''' + @idValue + '''' exec (@query)
    		  go
    Assuming that the query is not automatically parameterized, if you execute this procedure against thetitlestable in thepubssample database two times with different values for the@idValueparameter, SQL Server must generate a separate query plan for each execution. Например,:
    exec dynamicUsingEXECUTE
    		  'titles', 'title_id', 'MC2222' go exec dynamicUsingEXECUTE 'titles',
    		  'title_id', 'BU7832'
    Примечание.In this example, the query is simple enough that SQL Server can automatically parameterize it and actually reuse the execution plan. However, if this was a complex query that SQL Server may not automatically parameterize, SQL Server may not reuse the plan for the second execution if the@idValueparameter was changed. The following simple query limits the complexity of the example.

    You can rewrite this procedure to usesp_executesqlinstead of the EXECUTE statement. Support for parameter substitution makessp_executesqlmore efficient because it generates execution plans that are more likely to be reused by SQL Server. Например,:
    drop proc dynamicUsingSP_EXECUTESQL go create proc
    		  dynamicUsingSP_EXECUTESQL @table sysname, @idName varchar(10), @idValue
    		  varchar(10) as declare @query nvarchar(4000) -- Build query string with
    		  parameter select @query = 'select * from ' + @table + ' where ' + @idName + ' =
    		  @idValue' -- Now execute with parameter exec sp_executesql @query, N'@idValue
    		  varchar(10)', @idValue go exec dynamicUsingSP_EXECUTESQL 'titles', 'title_id',
    		  'MC2222' go exec dynamicUsingSP_EXECUTESQL 'titles', 'title_id',
    		  'BU7832'
    In this example, the first time that thesp_executesqlstatement is executed, SQL Server generates a parameterized plan for the SELECT statement fromtitlesэтим кодом:title_idas the parameter. For the second execution, SQL Server reuses the plan with the new parameter value. For more information aboutsp_executesql, see the "sp_executesql (T-SQL)" and "Using sp_executesql" topics in SQL Server 7.0 Books Online.
  • SP:RECOMPILESThis event indicates that a stored procedure was recompiled during execution. Many recompile events indicates that SQL Server is using resources for query compilation instead of query execution.
If you do not see any of these events, the application is executing only ad-hoc queries against SQL Server. Unless SQL Server determines that it can automatically parameterize certain queries or if the same parameters are used repeatedly, each query that is executed requires SQL Server to generate a new execution plan. SQL Server Performance Monitor should show many SQL Compilations/sec. This can be CPU-intensive for many concurrent users. To work around this issue, find the most frequently executed queries, and consider creating stored procedures for these queries, using parameter markers, or usingsp_executesql.

Ссылки

For more information about monitoring and troubleshooting performance issues in SQL Server, click the following article numbers to view the articles in the Microsoft Knowledge Base:
224587Устранение неполадок производительности приложения с помощью SQL Server
224453INF: Understanding and resolving SQL Server 7.0 or 2000 blocking problems
243586Устранение неполадок при перекомпиляции хранимых процедур
243589Способы устранения медленного выполнения запросов SQL Server 7.0 или более поздней версии
251004INF: Как для наблюдения за блокировки SQL Server 7.0

Свойства

Код статьи: 243588 - Последний отзыв: 18 ноября 2010 г. - Revision: 2.0
Информация в данной статье относится к следующим продуктам.
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard Edition
Ключевые слова: 
kbhowtomaster kbhowto kbinfo kbmt KB243588 KbMtru
Переведено с помощью машинного перевода
ВНИМАНИЕ! Перевод данной статьи был выполнен не человеком, а с помощью программы машинного перевода, разработанной корпорацией Майкрософт. Корпорация Майкрософт предлагает вам статьи, переведенные как людьми, так и средствами машинного перевода, чтобы у вас была возможность ознакомиться со статьями базы знаний KB на родном языке. Однако машинный перевод не всегда идеален. Он может содержать смысловые, синтаксические и грамматические ошибки, подобно тому как иностранец делает ошибки, пытаясь говорить на вашем языке. Корпорация Майкрософт не несет ответственности за неточности, ошибки и возможный ущерб, причиненный в результате неправильного перевода или его использования. Корпорация Майкрософт также часто обновляет средства машинного перевода.
Эта статья на английском языке:243588

Отправить отзыв

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com