Statistics in SQL Server in VS .NET

Generation QR Code JIS X 0510 in VS .NET Statistics in SQL Server

Statistics in SQL Server
QR-Code Generation In .NET Framework
Using Barcode generation for Reporting Service Control to generate, create QR image in Reporting Service applications.
Bar Code Creation In .NET
Using Barcode drawer for Reporting Service Control to generate, create bar code image in Reporting Service applications.
Let s assume you execute a query in SQL Server The query optimizer:
Paint QR Code ISO/IEC18004 In Visual C#
Using Barcode generation for .NET framework Control to generate, create QR Code image in .NET applications.
Making QR Code In .NET Framework
Using Barcode creation for ASP.NET Control to generate, create Denso QR Bar Code image in ASP.NET applications.
Parses it (makes sure it s syntactically correct) Resolves it (makes sure the referenced objects exist) Optimizes it (identifies the best plan and the best indexes to use) Executes it
Quick Response Code Creator In VS .NET
Using Barcode generation for VS .NET Control to generate, create Denso QR Bar Code image in .NET applications.
Denso QR Bar Code Printer In Visual Basic .NET
Using Barcode generator for Visual Studio .NET Control to generate, create QR Code JIS X 0510 image in .NET applications.
How does the query optimizer know which index to use during the optimization phase It uses statistics on the index to determine the density and selectivity Ideally, the indexes with the highest selectivity and lowest density are chosen for use when executing the query
Create EAN / UCC - 13 In VS .NET
Using Barcode maker for Reporting Service Control to generate, create USS-128 image in Reporting Service applications.
Bar Code Generation In Visual Studio .NET
Using Barcode creation for Reporting Service Control to generate, create bar code image in Reporting Service applications.
Density
Code 128A Creation In VS .NET
Using Barcode generation for Reporting Service Control to generate, create Code128 image in Reporting Service applications.
Data Matrix 2d Barcode Drawer In .NET Framework
Using Barcode printer for Reporting Service Control to generate, create ECC200 image in Reporting Service applications.
The density refers to the percentage of duplicate rows in an index Let s say we have a database of the Smith family Almost every member of the Smith family has the last name of Smith If we had an index on the LastName column, it would be very dense and not very useful for most queries, as shown in Table 7-1
Print Bar Code In Visual Studio .NET
Using Barcode drawer for Reporting Service Control to generate, create barcode image in Reporting Service applications.
Barcode Creator In .NET
Using Barcode generation for Reporting Service Control to generate, create bar code image in Reporting Service applications.
MCITP SQL Server 2005 Database Developer All-in-One Exam Guide
Making USPS Intelligent Mail In Visual Studio .NET
Using Barcode maker for Reporting Service Control to generate, create USPS Intelligent Mail image in Reporting Service applications.
Data Matrix 2d Barcode Scanner In Visual Studio .NET
Using Barcode decoder for .NET Control to read, scan read, scan image in VS .NET applications.
LastName
Creating UCC-128 In Java
Using Barcode generator for Android Control to generate, create GS1 128 image in Android applications.
Recognize Barcode In Java
Using Barcode Control SDK for BIRT reports Control to generate, create, read, scan barcode image in BIRT reports applications.
FirstName
Code 3 Of 9 Recognizer In VS .NET
Using Barcode decoder for Visual Studio .NET Control to read, scan read, scan image in .NET applications.
Scan Barcode In Visual Studio .NET
Using Barcode recognizer for VS .NET Control to read, scan read, scan image in .NET applications.
Smith Smith Smith Smith Smith Smith Toth
Make Code 128A In Objective-C
Using Barcode creator for iPhone Control to generate, create Code128 image in iPhone applications.
Encode UPC-A Supplement 5 In VB.NET
Using Barcode drawer for Visual Studio .NET Control to generate, create UPCA image in Visual Studio .NET applications.
Adam David Emily John Mary Sally William
Table 7-1 Sample Data from the Smith Family
An index can be very dense for one query and not very useful, or not dense for another query making it very useful Consider this query looking for John Smith:
SELECT * FROM FamilyTable WHERE LastName = 'Smith' AND FirstName = 'John';
In this case, the index is not useful and the query optimizer probably would not look at it Let s look at another query on the same table:
SELECT * FROM FamilyTable WHERE LastName = 'Toth' AND FirstName = 'William';
In this case, we have only one row that meets the query, so the index is not dense for this query and would be used Alternatively, consider a PK on a table A PK must be unique, so the density of an index on the PK is very low PK indexes are frequently used because the density is always low
Selectivity
Selectivity refers to the percentage of rows accessed or returned by a query Let s look at the Smith family table again, but this time with the PK added, as shown in Table 7-2
FamilyID LastName FirstName
1 2 3 4 5 6 1000
Smith Smith Smith Smith Smith Smith Toth
Adam David Emily John Mary Sally William
Table 7-2 The Smith Family Table with PK
7: Optimizing Databases
Consider this query:
SELECT * FROM FamilyTable WHERE FamilyID = 5;
The result of this query is one person and only one This index is very selective for this query and so the index would likely be used Consider this query:
SELECT * FROM FamilyTable WHERE FamilyID > 990;
Our table is 1000 rows, and the query returns only ten rows or 1 percent of them Again, our index is very selective for this query and would likely be used For a query that isn t selective, consider this query:
SELECT * FROM FamilyTable WHERE FamilyID < 900;
This query will return 899 rows or 90 percent of the table Instead of searching the index to find a row, and then getting the row s data, the query optimizer might choose to do a table scan instead While table scans aren t very efficient when we need to identify one of 1000 rows, they are efficient when we need to identify 90 percent of a table
Statistics on an Index
Now that we understand selectivity and density, let s cover which statistics are actually created Assume you have an Orders table with five million rows The query optimizer wants to know how dense an index is, or how selective an index is for a given query If it had to search the entire five million rows of each possible index, it may take longer to calculate which index to use than it actually takes when using a single index In Figure 7-16, we can see the OrderID index in comparison with the statistics on the index The actual index is five million rows, but the statistics on the index is only 300
Row # 1 2 3 4 901 902 4,999,999 5,000,000 OrderID 1001 1002 1003 1004 100,901 100,902 1,004,999,999 1,005,000,000 Row # 1 2 3 100 101 299 300 OrderID 1001 10,016,667 10,033,334 100,166,670 100,183,337 10,04,983,333 10,05,000,000
Copyright © OnBarcode.com . All rights reserved.