SQL Performance: Using SQL Server DMVs to Identify Missing Indexes

Reference: http://www.mssqltips.com/sqlservertip/1634/using-sql-server-dmvs-to-identify-missing-indexes/

Using SQL Server DMVs to Identify Missing Indexes

Problem
In a previous tip, Deeper insight into unused indexes for SQL Server, we discussed how to identify indexes that have been created but are not being used or used rarely. Now that I know which indexes I can drop, what is the process to identify which indexes I need to create. I can run the Database Tuning Advisor or examine the query plans, but is there any easier way to determine which indexes may be needed?

Solution
As with a lot of the new features we have seen with SQL Server 2005 and now with SQL Server 2008, Microsoft introduced a few more dynamic management views to also assist with identifying possible index candidates based on query history.

The dynamic management views are:

Like most of the statistics that are tracked for the DMVs, these basically work the same where once the instance of SQL Server is restarted the data gets cleared out. So if you are working in a test environment and restart your instance of SQL Server these views will probably return no data.

SQL: Understanding SQL Server Net Libraries

Reference: http://www.mssqltips.com/sqlservertip/2320/understanding-sql-server-net-libraries/

Understanding SQL Server Net Libraries

Problem

I know that SQL Server and its clients interact with each other using different Net-Libraries or network protocols. But what are these Net-Libraries that SQL Server uses and how do each of these differ from the others. Also, in which scenario is one preferred over the others?

Solution

SQL Server provides different Net-Libraries, known as Dynamic-Link Library (DLL), to let clients communicate with SQL Server using different network protocols. These Net-Libraries specify which networking protocols to use while exchanging data back and forth between SQL Server and the clients. SQL Server provides Net-Libraries for almost all the commonly used networking protocols namely TCP/IP, Named Pipes, Shared Memory, etc… These Net-Libraries transparently send the requests from the client to SQL Server and return responses back from SQL Server to the clients.

Depending on what Net-Libraries you have enabled on the SQL Server, it starts listeners for these Net-Libraries. You can configure SQL Server to listen simultaneously on any combination of Net-Libraries. In other words, when the net-library listener on the server receives the TDS (Tabular Data Stream which is the format in which client requests are broken/formatted before sending it to the server as well as server responses broken/formatted before sending it back to clients) packets from the network, it reassembles them into original client requests and passes it up for processing. Please note each specific type of SQL Server Net Library listener will listen for the client requests if they are coming from the same type of Net Library (a matching pair of Net-Libraries must be enabled on both SQL Server and clients) and a connection from the client can be done using a single protocol only. For example, if the client sends the request to SQL Server using TCP/IP then on the server only the TCP/IP Net Library listener will pick up this request. Also, SQL Server will use the same network stack, over which the request was received, to send the response back to the client.

These are the different Net-Library options that are available:

Shared Memory
When you have a client which is on the same physical machine as your SQL Server, you can use this simplest net library which provides faster communication than other Net-Libraries as it bypasses the physical network stack. The reason is, it uses the Windows Shared Memory feature to communicate between SQL Server and the client. You might use this Net-Library while troubleshooting if other Net-Libraries are not working properly. This Net-Library is by default enabled and used when you specify either a period or (local) as your machine name or localhost or machine name or by prefixing machineinstance name with lpc: when connecting to a SQL Server instance, you can find more details here.

TCP/IP
TCP/IP (Transmission Control Protocol/Internet Protocol) is the default protocol on the clients running on the Windows platform. It allows communication across interconnected networks of computers with diverse hardware and operating systems. TCP/IP is considered as the first choice after Shared Memory, because of its inherent network traffic routing standards and advanced security features.

Named Pipes
Named Pipes protocol uses a part of memory of one process to store/pass the information to another process, it means the output of one process becomes the input for another process. The other process could be either on the local machine or could be on a remote machine (networked machine). This protocol is used in fast local area networks for better performance.

VIA
VIA stands for Virtual Interface Adaptor. This protocol works with VIA hardware. Please refrain from using this protocol in your newer development as it has been deprecated and will be removed in future releases of SQL Server.

Normally TCP/IP is the preferred protocol in a slow LAN, WAN or dial up network whereas Named Pipes is more considerable in fast local area networks, to learn more about these differences click here.

The script provided in script #1 below gives the details about existing connections like when the connection was established and what protocol is being used by that particular connection.

–Script #1 – Identifying the protocols being used by current connections
SELECT connection_id, connect_time, net_transport, net_packet_size, client_net_address
FROM sys.dm_exec_connections

SQL: Using SQL Server DMVs to Identify Missing Indexes

Reference: http://www.mssqltips.com/sqlservertip/1634/using-sql-server-dmvs-to-identify-missing-indexes/

Using SQL Server DMVs to Identify Missing Indexes

Problem
In a previous tip, Deeper insight into unused indexes for SQL Server, we discussed how to identify indexes that have been created but are not being used or used rarely. Now that I know which indexes I can drop, what is the process to identify which indexes I need to create. I can run the Database Tuning Advisor or examine the query plans, but is there any easier way to determine which indexes may be needed?

Solution
As with a lot of the new features we have seen with SQL Server 2005 and now with SQL Server 2008, Microsoft introduced a few more dynamic management views to also assist with identifying possible index candidates based on query history.

The dynamic management views are:

Like most of the statistics that are tracked for the DMVs, these basically work the same where once the instance of SQL Server is restarted the data gets cleared out. So if you are working in a test environment and restart your instance of SQL Server these views will probably return no data.

SQL: Understanding SQL Server Statistics (Good One)

Reference: http://blog.idera.com/sql-server/understanding-sql-server-statistics/

Understanding SQL Server Statistics

If there’s an upcoming election and you are running for office and getting ready to go from town to town city to city with your flyers, you will want to know approximately how many flyers you’re going to bring.

If you’re the coach of a sports team, you will want to know your players’ stats before you decide who to play when, and against who. You will often play a matchup game, even if you have 20 players, you might be allowed to play just 5 at a time, and you will want to know which of your players will best match up to the other team’s roster. And you don’t want to interview them one by one at game time (table scan), you want to know, based on their statistics, who your best bets are.

Just like the election candidate or the sports coach, SQL Server tries to use statistics to “react intelligently” in its query optimization. Knowing number of records, density of pages, histogram, or available indexes help the SQL Server optimizer “guess” more accurately how it can best retrieve data. A common misnomer is that if you have indexes, SQL Server will use those indexes to retrieve records in your query. Not necessarily. If you create, let’s say, an index to a column City and <90% of the values are ‘Vancouver’, SQL Server will most likely opt for a table scan instead of using the index if it knows these stats.

For the most part, there *may* be minimal we need to do to keep our statistics up-to-date (depending on your configurations), but understanding statistics a little bit better is in order to help us understand SQL Server optimization a little bit more.

How are statistics created?

Statistics can be created different ways
– Statistics are automatically created for each index key you create.

– If the database setting autocreate stats is on, then SQL Server will automatically create statistics for non-indexed columns that are used in queries.

– CREATE STATISTICS

What do statistics look like?

If you’re curious, there’s a couple ways you can peek at what statistics look like.

Option 1 – you can go to your Statistics node in your SSMS, right click > Properties, then go to Details. Below is a sample of the stats and histogram that’s collected for one of the tables in my database

Option 2 – you can use DBCC SHOW_STATISTICS WITH HISTOGRAM

The histograms are a great way to visualize the data distribution in your table.

How are statistics updated?

The default settings in SQL Server are to autocreate and autoupdate statistics.

Notice that there are two (2) options with the Auto Update statistics.
Auto Update Statistics basically means, if there is an incoming query but statistics are stale, SQL Server will update statistics first before it generates an execution plan.
Auto Update Statistics Asynchronously on the other hand means, if there is an incoming query but statistics are stale, SQL Server uses the stale statistics to generate the execution plan, then updates the statistics afterwards.

However, if you want to manually update statistics, you can use either sp_updatestats or UPDATE STATISTICS <statistics name>

How do we know statistics are being used?

One good check you can do is when you generate execution plans for your queries:

check out your “Actual Number of Rows” and “Estimated Number of Rows”.

If these numbers are (consistently) fairly close, then most likely your statistics are up-to-date and used by the optimizer for the query. If not, time for you to re-check your statistics create/update frequency.

What configuration settings should we set?

There may be cases when you may want to disable statistics update temporarily while you’re doing massive updates on a table, and you don’t want it to be slowed down by the autoupdate.

However, for the most part, you will want to keep the SQL Server settings:
– auto create statistics
– auto update statistics

References:

Rob Carrol. http://blogs.technet.com/b/rob/archive/2008/05/16/sql-server-statistics.aspx

Elisabeth Redei has an excellent 3-part series on SQL Server Statistics:
http://sqlblog.com/blogs/elisabeth_redei/archive/2009/03/01/lies-damned-lies-and-statistics-part-i.aspx
http://sqlblog.com/blogs/elisabeth_redei/archive/2009/08/10/lies-damned-lies-and-statistics-part-ii.aspx
http://sqlblog.com/blogs/elisabeth_redei/archive/2009/12/17/lies-damned-lies-and-statistics-part-iii-sql-server-2008.aspx

Excellent Books that touch on statistics
– Apress. Grant Fritchey & Sajal Dam. SQL Server 2008 Query Performance Tuning Distilled.
RedGate. Holger Schmeling. SQL Server Statistics.

SQL: Difference Between SQL Vs. T-SQL?

Reference: http://blog-mstechnology.blogspot.in/2012/04/difference-between-sql-vs-t-sql.html

Difference Between SQL Vs. T-SQL?

SQL and T-SQL are Query languages, both helps to extract, Manipulate data with database system.

SQL – Structured Query Language:-

It is pronounced as SEQUEL, stands for… Structured English QUEry Languages.

  • SQL Language has several language elements called clauses, phrases, predicates, queries and statements.
  • SQL Supports to insert/update/read/delete the data from/to the database, define the new structure, etc.,
  • SQL supports different data types like image, text, varchar, datetime,xml, etc., SQL supports only single line transactions

T-SQL – Transact-SQL:-

T-SQL is advanced/addition to the SQL developed by Microsoft. It has new features like, variable, temp tables, set of transactions, store procedures, functions, cursors etc.., T-SQL supports control flow capabilities by using the following commands: START and TERMINATE, break, CONTINUE, GOTO, RETURN, IF, While and WAITFOR. T-SQL allows the ‘FROM’ clause to be added to DELETE and UPDATE statements. T-SQL allows inserting multiple rows using single statement BULK INSERT.

SQL: Exporting SQL Data in CSV format

Reference: http://ask.sqlservercentral.com/questions/7080/export-results-of-a-query-into-a-csvtxt-file.html

Method 1:

Straight to file

In Management Studio, open a query window and set the ‘results to file’ by either

· Query -> results to -> Results to file

· ctrl – Shift – F

· click the ‘Results to file’ icon on the toolbar

set the output to be comma separated

· (Tools -> Options -> Query results -> SQL Server -> Results to text -> Output format = ‘Comma delimited’

then run your query

select * from public_view where datefield = currentdate

Method 2:

In Management Studio, open a query window and set the results to text by either

· (Query -> results to -> Results to text

· ctrl – T

· click the ‘Results to text’ icon on the toolbar

set the output to be comma separated

· (Tools -> Options -> Query results -> SQL Server -> Results to text -> Output format = ‘Comma delimited’

then run your query

select * from public_view where datefield = currentdate

select all the results and paste into a file

Method 3:

Use BCP

probably best to review this in BOL

xp_cmdshell 'bcp "query" out filename.csv'