Size of The Index

Posted on by John HLeave a Comment on Size of The Index

At the end of last post, I gave you a puzzle and also mentioned to give the answer of it.

use AdventureWorks2008R2

--set statistics io on -- this is the hint
select CustomerID, SalesOrderID
from Sales.SalesOrderHeader with (index=[IX_SalesOrderHeader_CustomerID])
where CustomerID = 29974 and SalesOrderID = 45785

select CustomerID, SalesOrderID
from Sales.SalesOrderHeader
where CustomerID = 29974 and SalesOrderID = 45785

Look at the query plan, the cost of both are the same. Does it mean the performance of those 2 are the same? If not, Which statement generates better plan?

Continue reading “Size of The Index” →

Query Plan (06) – Seek

Posted on by John H2 Comments on Query Plan (06) – Seek

Seek operator presents both physical and logical operator. It can only apply to an index, clustered index or none clustered index. It’s the most efficient operation to reach a record in an index by key. As we know, indexes are organized in a B-Tree, each record in the non-leaf level of B-Tree includes a the first key(s) in the page of next level and the pointer (File:Page:Slot) of the page in the next level.

Continue reading “Query Plan (06) – Seek” →

Prepare Your Microsoft Certified Master – SQL Server Exam

Posted on by John H2 Comments on Prepare Your Microsoft Certified Master – SQL Server Exam

Microsoft Certified Master of SQL Server is the highest Microsoft techinal certification in the world. People getting certified by passing both written and lab exams are proven to possess deepest technical knowledge of SQL Server and be able to deal with complex scenarios, provide better solutions, and toubleshoot effectively. Borrow the words from Rober Davis’ slide deck

No matter what the situation, no matter what the customer, I can send a Master and everything will be ok.

Continue reading “Prepare Your Microsoft Certified Master – SQL Server Exam” →

Query Plan(03) – Operators

Posted on by John H2 Comments on Query Plan(03) – Operators

Query plan constists of set of steps as I mentioned previously. These steps also called operators. The data flows from right operators to left. Each operator can also be called node. The SELECT is not a node. The node number is from left to right, for instance, Filter is Node 0, Compute Scaler is Node 1, and Clustered Index Scan is Node 2. By looking at this plan, you might roughly tell what the query does behind – The data is acquired by scanning a clustered index on syscolpars table(Clustered Index Scan operator). Rows are sent to Compute Scalar operator for calculations. After the calculation rows are sent to filter operator, data cannot match the condition will be discarded and data satisfis the predicates will be output to client.

Continue reading “Query Plan(03) – Operators” →

Implementing IDataReader

Posted on by John H1 Comment on Implementing IDataReader

Last post, I talked about Customized Event and setting up trace for web applications which usually use pooled connection. Assuming you already have everything setup, SqlCommand component has been wrapped up to send an customized event with user or session information before an actually procedure call taking place. The trace is also setup to receive both procedure calls and customized events. To simplify the scenario, trace only capture the customized event and RPC Complete with column SPID, TextData, StartTime. To receive the trace data, you can either save them to the disk then read the trace file from the disk or directly stream the trace data.( see my post here for detail). After trace data being read, there will be always a customized event prior to an actual procedure call. When you are asked to get SQL Server response time for an particular application, for instance, give me average respose time of procedure ABC every 30 minutes during the day for that user, you can query the table includes the trace rows to locate all procedure ABC, then based on SPID and identity values to find previous record to get the user information of the procedure call, perform filter, aggregates…It will be fine if your table is small. The trace system generate more than 500MB data every hour, performing such query will be very challenging irrespective of how you design the indexes on the trace table. Now you may think to re-format the trace data to table with columns, EventID, SPID, TextData, StartTime, and UserInfo to simplify the query. But SQL Profiler cannot do it in that way, you will have to write an ETL for that.

Continue reading “Implementing IDataReader” →

Customized Event

Posted on by John HLeave a Comment on Customized Event

SQL Profiler is a wonderful tool being wide used for system monitoring, performance monitoring, debugging, etc. ( you can count more than I do ;), I better shut up.) It provides tons of events triggered by SQL Server from different areas for various purposes. You are allowed to create customized event for your own by calling sp_trace_generateevent. There is already an example in BOL that you can trace get notified whenever a record is being inserted into a table. It quite useful when you try to centralize the error messages or some other stuff. Well, if your database is serving a busy website where the user management is application type user permission management, you are asked to keep track of every procedure calls with application user tied with, customized event will come to play. This was the one I have done before. Please feel free to tell me your story with customized events by replying my post.

Continue reading “Customized Event” →

Query Plan (02) – Set of Steps

Posted on by John HLeave a Comment on Query Plan (02) – Set of Steps

Query plans are merely set of steps that will be running in specific order to achieve user defined operation(s). Such ideas are widely presented in all known and unknown areas. In order to light up a fire, someone might find a piece of stick, a rock and hays, scratch the stick on the rock, make the temporature of the stick high enough to light itself or the hays. But you may just go to grocery store to buy matches or lighters and use them to burn the hays. Mapping those 2 approaches in SQL Server, those are 2 query plans.

Continue reading “Query Plan (02) – Set of Steps” →

Query Plan (01) – T-SQL vs Programming Language

Posted on by John HLeave a Comment on Query Plan (01) – T-SQL vs Programming Language

A computer is a programmable hardware unit with many components integrated. CPU is one of the most important components which provides set of functionalities for calculations and memory/IO access. CPU can load data from memory to registries(memory units in CPU), perfrom calculation based on loaded data, and then following instructions within loaded data saves the result back to memory. Let’s say you want to add 2 values together, assuming they are all in the stack. You need to move them to AX and BX registry, perform ADD, and then move result back to stack.

POP AX //read an integer from stack to AX register
POP BX //read another integer from stack to BX register
ADD AX,BX // add 2 32-bit itegers together and save the result into AX
PUSH AX //save result back to stack

After result is saved to the stack, other functions can POP and perform code to show it on the screen. (back old days when MS DOS was prevalence, INT 21 can show charactors on the screen).

Continue reading “Query Plan (01) – T-SQL vs Programming Language” →