SQL Quiz
Saya hendak memberikan 1 jatah berlangganan technet gratis selama setahun kepada peserta milis melalui quiz SQL ini dengan menampilkan 5 long running query lewat DMV, kolom-kolom yang perlu ditampilkan adalah waktu eksekusi maksimum, waktu eksekusi rata-rata, single query statement dan batch query statement dimana terdapat single query statement tersebut.
Ketentuan Quiz:
- Yang berhak mengikuti quiz adalah peserta mailing list kecuali karyawan Microsoft dan MVP.
- Kirim jawabannya lewat posting-an ini dengan menyertakan alamat email kantor.
- Peserta yang menjawab paling tepat dan paling awal urutannya dipilih sebagai pemenang.
- Quiz ini ditutup pada tanggal 16 Mei 2008 jam 18:00 WIB.
- Pemenang akan dihubungi lewat email kantor untuk keperluan pengisian nama perusahaan dan alamat pengiriman paket technet.
Technet Plus Direct provides fast and easy online access to essential tools and resources for IT Professionals including :
1. All Microsoft commercial software for evaluation with no time limits.
2. Two complimentary Professional Support incidents.
3. The latest betas before public release.
4. Extensive technical and support resources -- updated regularly.
Debugging Occasionally Error with
SQL Trace
By : Kasim Wirama, MCDBA
You have created a stored procedure and you tested your
stored procedure works fine in testing environment. After deployment, your
stored procedure sometimes doesn’t work as expected, let’s say the stored
procedure sometimes doesn’t insert new record and you need to know the data why
it doesn’t work occasionally. To reproduce the problem it is difficult because
it intermittent behavior. The way to debugging the stored procedure is by using
Profiler with Stored Procedures:RPC:Starting, TSQL:BatchStarting, and UserConfigurable
event (let’s say use UserConfigurable:0). UserConfigurable event is triggered
under sp_trace_generateevent system stored procedure, which should have
appropriate ALTER TRACE permission.
It is not good practice to assign user’s login with ALTER
TRACE permission regarding to sp_trace_generateevent. The trick to bridge server
level permission and database level permission is to create wrapper stored
procedure for sp_trace_generateevent and sign the wrapper stored procedure with
certificate. Besides that you need to create a login based on certificate in
master database and backup-restore the certificate together its private key to
target database.
First, create certificate in master database. In this
example I create certificate with name mycert, subject=’testing certificate’
with start date and expiry date between 1 Jan 2008 and 31 Dec 2008.
CREATE CERTIFICATE mycert ENCRYPTION BY PASSWORD=’123’ WITH
SUBJECT=’testing certificate’, START_DATE=’20080101’, EXPIRY_DATE=’20081231’;
Then you create login based on the certificate and grant
ALTER TRACE and AUTHENTICATE SERVER permission to it.
CREATE LOGIN mylogin FROM CERTIFICATE mycert;
GO
GRANT ALTER TRACE TO mylogin
GO
GRANT AUTHENTICATE SERVER TO mylogin
GO
Backup your certificate together with its private key and
restore to target database (in my sample here I use AdventureWorks database)
where your problematic stored procedure resides.
BACKUP CERTIFICATE mycert TO FILE=’C:\mycert.cer’ WITH
PRIVATE KEY (FILE=’C:\mycert.pvt’, ENCRYPTION BY PASSWORD=’123’, DECRYPTION BY
PASSWORD=’123’);
USE AdventureWorks;
GO
RESTORE CERTIFICATE mycert FROM FILE=’C:\mycert.cer’ WITH
PRIVATE KEY (FILE=’C:\mycert.pvt’, ENCRYPTION BY PASSWORD=’123’, DECRYPTION BY
PASSWORD=’123’);
Next you create stored procedure wrapper for sp_trace_generateevent
system stored procedure and sign it with the just-restored certificate.
CREATE PROCEDURE trace_debug
(
@eventid INT,
@userinfo NVARCHAR(128),
@userdata VARBINARY(8000)
) AS
EXECUTE sp_trace_generateevent @eventid = @eventid, @userinfo = @userinfo,
@userdata = @userdata;
GO
ADD SIGNATURE TO trace_debug BY CERTIFICATE mycert WITH
PASSWORD=’123’;
Give execute privilege on trace_debug stored procedure to
public database role.
GRANT EXECUTE ON trace_debug TO [public];
You alter the problematic stored procedure by adding
trace_debug execution after the line of problem suspect, for example after insert
statement with the following code :
IF @@ROWCOUNT = 0
EXEC trace_debug 82, ‘insert failed’,0x0000;
When trace_debug is executed, it will trigger
UserConfigurable:0 event (under User configurable event category) because I
assign value 82. If you assign value 83, it will trigger UserConfigurable:1
event and so forth.
You monitor the problematic stored procedure by installing
the following SQL trace events : StoredProcedures:RPC starting, TSQL:Batch
Starting and User Configurable:UserConfigurable:0.
Useful Events in Profiler
By : Kasim Wirama, MCDBA
Profiler is excellent tool provided to monitor SQL Server performance and troubleshoot SQL Server database objects. If you open profiler, you will be presented with many events, you don’t have the use most of the events to do particular task. I will show you most used events here :
1. Errors and Warnings event category
To get to know whether there are time out connections, you can choose Attention event. If you need to have errors raised from stored procedure/function/SQL batch, choose Exception and User Error Message event.
2. Locks event category
If you want to monitor whether deadlock happens on your SQL, choose DeadLock Chain event and DeadLock Graph (only in SQL Server 2005). Deadlock graph provides visual representation of deadlock, I see that the visual representation provides valuable information and it is nice feature. If you need to get information how long a resource is held before deadlock occurs and kinds of lock during the deadlocked transaction, you can install Lock:Acquired, Lock:Released, Lock:Escalation events.
3. Performance event category
In terms of performance tuning, to get statistics information and number of actual row counts, ShowPlan XML Statistics Profile event is the most useful one.
4. Security Audit event category
For security monitoring, such as failed login attempts, access to specific database objects, or restarting server, use events in the category. The other nice thing that SQL Server 2005 provides is that the capability to check whether a connection is coming from pooled connection, by looking at value 2 at EventSubClass of Event Login and Event Logout.
5. Stored Procedures event category
With point 3 and to capture RPC (remote procedure call) information, you need to install RPC:Starting and RPC:Completed. And to trace nested and complex stored procedure, it is necessary to install SP:StmtStarting and SP:StmtCompleted. Be careful these 2 events, they will burden I/O because they produces a bunch of information especially in busy server.
To get to know whether you SQL Server suffers under excessive recompilation and the stored procedures that dominate recompilation, you can look recompilation information from SP:Recompile.
6. TSQL event category
To get how much a query batch consuming CPU and I/O read-write and how long it completes the execution, you can get the information from SQL:BatchStarting and SQL:BatchCompleted events and include Duration, Reads, Writes and CPU column.
7. Transactions event category
To track that transaction information such as no commit/rollback to the corresponding begin transaction with given spid, you can look at SQLTransaction event and it gives the information at EventSubClass column with 0,1 or 2 (begin transaction, commit or rollback transaction).
Query Processor Engine in SQL Server
By : Kasim Wirama, MCDBA
Are you interested with SQL Server engine internal? If yes, I would like to show you what you should know about what query processor engine is and how it works.
Query processor engine is one of core components in SQL Server. Together with SQL Server storage engine, it processes query request. If I look it deeper into query processor, it consists of 2 sub components, i.e.: query optimizer and query execution engine. Query optimizer provides, as possible, the cheapest and the most efficient query plan. Query execution engine takes query plan and process it, including data read and writing to tables and indexes in coordination with SQL Server storage engine. Besides the task, query execution engine performs join and aggregation tasks.
What does query plan look like? If you see graphical representation of query plan in SSMS (by clicking “Include Actual Execution Plan” or “Display Estimated Execution Plan”), it consists of a set of icons. Those icons are called operators. Operators are independent among others. Each of them has specialized tasks and has no knowledge about other operators’ task. It has one output and none or more children.
Operators are classified into 3 categories; memory consuming operator, unblock and blocking operator and dynamic cursor support operator. A memory consuming operator usually stored result in temporary work table. The examples of memory consuming operator are sort, hash join and hash aggregate. Unblocking operator has capability to receive input and give output at the same time. Unblocking operator is not memory consuming operator and it is optimal in busy OLTP environment, for example EXISTS and TOP sub clauses are recommended in scenario that user need to check whether a criteria should be met within a query. Blocking operator will receive all input rows and then produces output. More often than not, blocking operator is also memory consuming operator. But there are blocking operators that do not memory consuming operator, for example COUNT aggregate function. The criteria of operator that supporting dynamic cursor is that it can save and restore its state, be able to scan forward and backward, have only one input row and non-blocking nature. GROUP BY sub clause doesn’t provide support for dynamic cursor.
A Lesson about Index Ordering
By : Kasim Wirama, MCDBA
I have posted articles about index in SQL Server 2005. They are important to be understood the way they access qualified records, but there are other aspect you need to consider, it is index ordering.
Index ordering determines how efficient your index is. In worst case, the optimizer will consider scanning over your index from first leaf page until last leaf page instead of just directly go to the relevant leaf pages even if you have given clue to your optimizer with appropriate search argument.
I had discussion with a DBA regarding to this case. Given a table that contains country code, product id, and price. To get the price fast, the user should give information about country code and product he would like to enquire. I agree with the DBA that country code and product id should be in an index. The DBA decided the index ordering should be country code and then followed by product id. What was the reason behind that? Here is the DBA’s quotation: “Because the user in respective country will access the product for the respective country as well, first index on country code would be recommended, so just relevant pages will be in data cache. I prefer not to fill up the data cache with other countries as well.” It makes sense for me.
The knowledge taken from the experienced DBA about this ordering is that your user access pattern and database design will determine index ordering for fine tune database performance.
Query dari 23 jam ke 7 detik lalu ke 2 detik; Ketawa ngakak
By : Kasim Wirama, MCDBA
Saya coba ceritakan sedikit mengenai pengalaman tuning. sebulan lalu saya ditelpon dari salah satu karyawan sebuah perusahaan di Sydney. Sepertinya dia sering baca ke dalam blog-ku, dan meminta bantuan untuk melihat kasus sebuah stored procedure, yang jika dieksekusi akan memakan waktu 23 jam. What the hell!, hatiku berguman. Ngeri juga terima permintaan bantuan dia, saya coba direct dari jauh dengan harapan dia berhasil melakukan. Namun harapanku meleset, terpaksa saya memenuhi undangan dia ke kantornya untuk trouble shoot stored procedure “keramat” tersebut yang didominasi oleh big size table sekitar 10 sampai 40 juta record. Terus terang saya cuma main-main dengan database dengan jumlah record 500 ribuan. Sempat keder juga. Tapi kupikir-pikir cuma 1 stored procedure aja, ladenin aja tantangannya.
Karena saya enggan utak-atik querynya karena ada 6 query di dalam stored procedure dan correlated subquery sampai 4 tingkat (#$#&%^#$@#), saya gunakan cara paling mudah dengan jalankan database tuning advisor (DTA), ternyata improvementnya cuma 4 persen bila diapply index dan statistik yang direkomendasikan oleh DTA. Lemas sudah saya, meski AC-nya dingin, namun saya keringat dingin juga, karena reputasi ikut dipertaruhkan juga. Dengan bahasa inggris yang standard Indonesia (sempat kelepasan kata “gua”, orang bulenya pasang tampang bingung), aku minta ijin kalau boleh utak-atik querynya. Dengan anggukan penuh semangat, dia bilang “Sure! you can do it”. Sampai malam jam 8 saya rombak querynya, ternyata usahaku tidak sia-sia, hasil akhir bisa sampai 2 detik dengan hot cache dan 3 detik dengan cool cache, dengan hasil permulaan rewrite query ke 7 detik. Orang bulenya terpana, saya juga ikut terpana. Terima kasih Tuhan akhirnya Engkau membantuku.
Malam itu udara Sydney dingin sekali karena masuk musim gugur, sama dinginnya dengan ruangan server. Antara mengigil dengan ketawa ngakak bercampur baur dalam perjalanan pulang ke apartemen. Sudah gilakah saya? Ya saya sudah gila karena merugi tidak ikut kuliah selama 1 hari, terpaksa fotocopy catatan dari sesama mahasiswa. Seminggu kemudian saya mendengarkan seminar performance tuning di SQL monthly meeting di salah sudut gedung pencakar langit sambil senyum-senyum ingat kejadian yang sudah lewat.
Get Information about SQL Trace
By : Kasim Wirama, MCDBA
SQL Server has many tools for monitoring its performance. One of the most used tools is profiler. Actually profiler engine uses SQL Trace. In this article, I would show you how you get information from your running trace.
1. Catalog view sys.traces You can see all SQL traces running on your SQL Server, by issuing this SQL command against catalog view below :
SELECT status, path, max_size, buffer_count, buffer_size, event_count, dropped_event_count FROM sys.traces;
2. Function fn_trace_geteventinfo This result of the function gives information about list of event and column that a trace is currently running. A trace is identified by trace id, for example if you have trace id is 2, you can get information from the function as below :
SELECT * FROM fn_trace_geteventinfo(2);
3. Function fn_trace_getfilterinfo If you apply filtering on profiler or SQL trace, you see information from the function with input parameter SQL trace id as the following example :
SELECT * FROM fn_trace_getfilterinfo(2);
4. Function fn_trace_gettable If you want to have get profiler/SQL trace while running, you can see information by querying the function.
For example if you put your trace file to C:\mytrace.trc, you issue the following query :
SELECT * FROM fn_trace_gettable (‘c:\mytrace.trc’,1);
5. Dynamic management view : sys.trace_events and sys.trace_columns Sys.trace_events contains list of eventname together their id, so does sys.trace_columns for column information.
Finally, if you run SQL trace, remember to stop and remove the SQL trace from SQL Server, otherwise your disk space is still claimed by SQL trace or your disk space will be entirely filled up. To stop SQL trace, supply value 0 to second input parameters and to remove SQL trace from SQL Server, supply value 2 to second input parameters. for example, if your running trace id is 3, here is the query command to stop and to remove respectively
EXEC sp_trace_setstatus 3,0
EXEC sp_trace_setstatus 3,2
Customize Black Block Trace
By : Kasim Wirama, MCDBA
SQL Server has background running trace by default that records some events. It is useful when your SQL Server or its hosting environment had crashed, so you can look some queries and investigate them right before the failure. It listens for SP:Starting, SQL:BatchStarting, Exception and Attention events, with some columns for each of the events.
You can enable black box trace by setting @options parameter = 8 from system stored procedure sp_trace_create. And you run the event by executing sp_trace_setstatus with status = 1 and passing @traceId value, here is the script :
DECLARE @traceid INT;
EXEC sp_trace_create @traceid OUTPUT, @options = 8;
EXEC sp_trace_setstatus @traceid, 1
By default, it generates 2 trace files, and flip back and forth between them. Size of each is 5 MB and located in default DATA folder of SQL Server installation directory. For example here I change default size 5 MB to 10 MB into @maxfilesize parameter.
DECLARE @traceid INT, @maxfilesize BIGINT;
SET @maxfilesize = 10
EXEC sp_trace_create @traceid OUTPUT, @options = 8, @tracefile = NULL, @maxfilesize = @maxfilesize;
EXEC sp_trace_setstatus @traceid, 1
When you specify @maxfilesize, you need to pass value for @tracefile even though the value is NULL.
You can make your black box trace start automatically when SQL Server start by wrapping it inside a stored procedure and create the stored procedure in master database. Then you execute sp_procoption system stored procedure below :
EXEC sp_procoption ‘your stored procedure’, ‘STARTUP’, ‘ON’
Detecting Blocking Problem
Detecting Blocking Problem
By : Kasim Wirama, MCDBA
SQL Server uses locks as ACID
implementation. Locks ensure data consistency in multiuser environment in
database. Locks become issue when several processes waiting for a resource that
is currently held by incompatible lock. In this situation, a process blocks
other processes to access shared resource. When your database had excessive
locking issue, you need to find out immediately and resolve it. I will tell you
how you monitor your server regarding to blocking with performance monitor and
DMV for more information about blocking.
You can have information from
these performance counter name below to get sense about current situation for
blocking. They are : SQLServer:Locks: Average Wait Time, SQLServer:Locks: Lock
Requests/sec, SQLServer:Locks: Lock Wait Time, SQLServer:Locks: Lock Waits/sec,
SQLServer:Locks: Number of Deadlocks/Sec, SQLServer:General Statistics:
Processes Blocked and SQLServer:Access Method: Table Lock Escalations/Sec.
Besides these counters above, you
can get history of shared lock and exclusive lock of pageiolatch by querying
DMV sys.dm_os_wait_stats
SELECT wait_type, waiting_tasks_count,
wait_time_ms_max_wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
The wait type you need to notice
is LCK_M_S, PAGEIOLATCH_SH and PAGEIOLATCH_EX. Compare wait_time_ms and
max_wait of them, if percentage of max_wait againsts wait_time_ms is large, you
need monitor blocking events from profiler.
Another DMV that is useful to
detect current blocking is sys.dm_tran_locks. To get the query involved in
blocking, you need to join the DMV with other DMVs, i.e.
sys.dm_os_waiting_tasks and sys.dm_exec_requests.
Detect Memory Bottleneck in SQL Server
By : Kasim Wirama, MCDBA
This article, I would like to give information about memory bottleneck at SQL Server. The easiest way to know whether your SQL Server undergoes memory bottleneck is by looking at Task Manager, tab Performance, Physical Memory section. Compare the Total and Available value, as long as Available is over 100 MB, it is still okay. If Available value is under 10 MB, your database server box is definitely under physical memory pressure. Besides physical memory pressure, there is virtual memory pressure. Let’s take a look for these kinds of pressure and see what solutions addressed to the corresponding problems.
You can get information about physical memory pressure by looking at these performance monitor counters: Memory:Available bytes, SQL Server:Buffer Manager: Buffer Cache Hit Ratio, Page Life Expectancy, Checkpoint Pages/sec, and Lazywrites/sec. You have your system performing well if value of first two counters are high and last two counters are low.
You can check how many memories allocated for buffer pool by issuing DBCC MEMORYSTATUS query in SSMS. Compare value between Committed and Target, if Target value is low, it indicates external physical memory pressure. You need to find out whether other application that causes external memory pressure, besides SQL Server, by looking at maximum memory consumed in Process tab of Task Manager window. If other application causes external memory pressure, it is advisable that the application is separated from database server box, or you add more memory. You need to check internal memory pressure by looking at percentage of stolen page to total committed page from DBCC MEMORYSTATUS. Percentage value is greater than 75% or 80% indicating internal physical memory pressure. Interesting thing about internal memory pressure is that you need to find out what components causing so many stolen memory page by querying this DMV below :
SELECT type, SUM(single_pages_kb) FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC
From query above, single page allocation takes memory from SQL Server buffer pool, whereas multipage allocation allocates memory outside buffer pool. Multipage allocator possibly causes internal memory pressure. You can get information about memory allocated to multipage page allocation by querying DMV below :
SELECT type, SUM(multi_pages_kb) FROM sys.dm_os_memory_clerks
WHERE multi_pages_kb != 0
GROUP BY type
ORDER BY SUM(multi_pages_kb) DESC
To detect virtual memory pressure, you can look at performance counter name here : Paging File: %Usage and Memory:Commit Limit.
Some solutions addressed to virtual memory pressure. They are:
1. Increase size of page file
2. Use /3GB options
3. Switch to 64 bit machine that have 8 TB memory address space.
Detecting and Resolving I/O Bottleneck
By : Kasim Wirama, MCDBA
I/O subsystem is one of critical component in SQL Server. I/O subsystem is used when SQL Server moves page between memory and I/O subsystem. With intensive activity of DML and DDL, SQL Server generates significant log entries and to tempdb database if the activities are creation and operations of table variable, temporary table, sorting, create and rebuild indexes and row versioning technology. Let’s look how to detect I/O bottleneck and solution to this issue.
Here are performance counter for detecting I/O bottleneck :
1. PhysicalDisk: Avg. Disk Queue Length
If you encounters value 2 or more when SQL Server is under peak usage, you have I/O bottleneck.
2. PhysicalDisk: Avg. Disk Sec/Read and Avg. Disk Sec/Write
These counter names gives information about average value on how fast your disk operates under read and write activity. You need to pay attention for I/O subsystem when the value is more than 20 ms.
3. PhysicalDisk: Disk Reads/sec and Disk Writes/sec
These counter names gives rate of read and write operation. If the value is at least 85 percent of disk capacity, the I/O subsystem experiences bottleneck.
Unfortunately these counters above measures I/O subsystem on hard disk level not in file level. If you have several files in an I/O subsystem, you need to have information from DMV sys.dm_io_virtual_file_stats, looking at io_stall_read_ms and io_stall_write_ms. Run the DMV several times in intended duration to get delta of these values.
You might have 3 possibilities of I/O subsystem issue. It might be caused by inefficient queries that effects to I/O intensive operations, lack of indexes or the disk subsystem needed to be upgraded to accommodate anticipated workload. You can find out I/O intensive query by querying DMV sys.dm_exec_query_stats and sort descending order for sum of total_logical_reads and total_logical_writes. To find out lack of indexes in an underlying table you issue DMV query below :
SELECT t1.object_id, t2.user_seeks, t2.user_scans, t1.equality_columns, t1.inequality_columns
FROM sys.dm_db_missing_index_details AS t1, sys.dm_db_missing_index_group_stats AS t2, sys.dm_db_missing_index_groups AS t3
WHERE database_id = DB_ID(‘your database’) AND object_id = OBJECT_ID(‘your table’) AND t1.index_handle = t3.index_handle AND t2.group_handle = t3.index_group_handle
SQL Server 2005 exposes I/O performance information through least performance impact DMV, so that you can quickly spot and fix the I/O bottleneck issue.
Detect CPU Bottleneck in SQL Server
By : Kasim Wirama, MCDBA
When you have your database server experiencing a problem, there are many possibilities here, such as CPU, memory, hard disk or database configuration itself. There should be a systematic way to trouble shooting performance problem in SQL Server. This article, I would like to point out how to detect bottleneck in CPU with SQL Server 2005.
The straight forward way to detect CPU problem is to look at performance counter, with object : Processor, and counter name : %Processor Time. If it shows high percentage value, let’s say 80% or over during 15 to 20 minutes, you definitely have CPU bottleneck. Anyway, you need to establish your baseline for CPU threshold above. Another counter name that is useful is System:Processor Queue Length. This counter gives information how long a queue for each processor. If you see 2 or more value for most of the time, your processors are under pressure. When your server box has some applications running besides SQL Server, probably one of the application takes up significant CPU resource. To prove your suspicious thought, get information from Process:%Processor Time counter.
If you have your CPU bottleneck caused by SQL Server, you need to find out how many processes that are running, runnable, and suspended. An amount of runnable processes indicate that the CPU is busy serving other request, and an amount of suspended processes indicate that there is blocking issue. Here is the query to get the information.
SELECT COUNT(*) , t2.scheduler_id
From sys.dm_os_workers as t2, sys.dm_os_schedulers as t2
Where t1.state = ‘runnable/running/suspended’ and t1.scheduler_address = t2.scheduler_address and t2.scheduler_id < 255
Group by t2.scheduler_id
In general, there are 2 things that causes CPU bottleneck, they are :
1. Inefficient query plan.
If you want to associate the query with CPU bottleneck, you query it from DMV sys.dm_exec_query_stats and extract query text from sys.dm_exec_sql_text with parameter sql_handle. You sort the result based on most expensive average CPU cost that consists of division between total_worker_time and execution_count
2. Excessive compilation and recompilation.
If SQL Server needs some time to compile/recompile the query, it shows that your execution plan is not reusable. If your query is very complex, try to rewrite/adding some index that will make the compilation time run faster.
These are 3 performance counter relating to excessive compilation/recompilation issue :
a. SQL SERVER: SQL Statistics : Batch Requests/Sec
b. SQL SERVER: SQL Statistics : SQL Compilations/Sec
c. SQL SERVER: SQL Statistics : SQL Recompilations /Sec
With wealth information from DMV and performance monitor, you have a useful tool for troubleshooting CPU bottleneck right away.
Statistics time for Plan Reuse
By : Kasim Wirama, MCDBA
In my early days of query tuning during my IT career, I seldom used STATISTICS TIME for query tuning. But recently I just know that statistics time indeed gives useful information. This time statistics gives indication whether your query plan is reusable and how long it takes for compilation and optimization phase. I would tell you why I say it is valuable tool for query tuning.
If you execute a query after you turn on statistics time, you will get information how long the query is parsed-compiled and executed. Even, it gives granular information how long CPU gets involved in the process and the elapsed time.
For plan reuse, you will see the two sets of data resulted from turning on STATISTICS TIME. They show information the execution plan is added to plan cache for reuse to other similar query request. First set shows parse and compile activity, second set shows SQL Server retrieves plan cache. CPU time is not equal for first execution. If second execution results zero value for CPU time, it shows good indication that your query uses existing execution plan.
Information from statistics time should be combined with information from IO and Profile statistics to give precise information how exactly query processing happened. Statistics time with other statistics information (IO, PROFILE, XML) takes effect for only current session
Read Ahead Read and Scan Count
By : Kasim Wirama, MCDBA
If you turn STATISTICS IO on to get know access to underlying tables in a query, you have information logical read, physical read, read ahead read and scan count. Besides logical read and physical read, other interesting information are read ahead read and scan count. What information that they can give? Let take a look.
Read ahead reads indicates number of pages that are read into data cache using read ahead mechanism. Notice that if read ahead reads is high, probably the physical read is low and buffer cache hit ratio is high. In this situation, additional memory into SQL Server benefits for read ahead read. It is a good indication, but it is better that the data will be in cache from previous execution so no additional data pages requested for read ahead activity. Read ahead read is an asynchronous operation; it means that it doesn’t block the query to complete as long as the query has the pages when needed. Read ahead mechanism has relation to full or partial table scan. In the scan, the IAM table is accessed in disk order to determine which extents are belonged to underlying database object. If you put eight files in a filegroup, the read ahead reads will keeps those files busy instead of sequentially processing those files.
Scan count indicates the number of times a table is accessed. In nested loop, even though scan count of inner table is one but logical read of inner table is calculated as multiplication of number scan count of an outer table is accessed with number of logical read of corresponding inner table. For merge or hash join, the scan count is one, but memory for those joins are bigger than nested loop join. The amount of memory that is consumed can be inspected in column granted_query_memory in DMV sys.dm_exec_requests, but it is only current session, if you want to see all sessions, you inspect column memory_usage in DMV sys.dm_exec_sessions.
Logical and Physical Read in SQL Server Performance
By : Kasim Wirama, MCDBA
Every query has its execution plan before it gets executed. Execution plan is generated by optimizer, one of components in SQL Server database engine; it contains how to access data to satisfy the query request. You probably meet few queries having slow response time by looking into physical read and logical read. How do you get information about logical and physical for a query? I will tell you how and what it means to your query.
You open a connection in SSMS (SQL Server Management Studio) or Query Analyzer, then execute SET STATISTICS IO ON. After that you can execute query that is slow. And you will get information logical and physical read on a related table(s).
Logical read indicates total number of data pages needed to be accessed from data cache to process query. It is very possible that logical read will access same data pages many times, so count of logical read value may be higher than actual number of pages in a table. Usually the best way to reduce logical read is to apply correct index or to rewrite the query.
Physical read indicates total number of data pages that are read from disk. In case no data in data cache, the physical read will be equal to number of logical read. And usually it happens for first query request. And for subsequent same query request the number will be substantially decreased because the data pages have been in data cache.
Buffer hit ratio will be calculated based on these two kinds of read as the following formula: (logical reads – physical reads)/logical read * 100%. The high buffer hit ratio (if possible to near 100%) indicates good database performance on SQL Server level. So use information from physical read and buffer hit ratio to measure performance in server level and logical read to measure individual query level
More Posts
Next page »