Using SET STATISTICS TIME ON

The SET STATISTICS TIME command is used in order to identify parse, compile, and execution times that occur on the server-side. It is important to note, these times do not include the time it takes to transfer the data to the client. In order to turn on, simple execute the statement: [cc lang=”sql”] SET STATISTICS TIME ON [/cc] Once the statement is executed, it is turned on for the entire session, and will stay on until it is turned off. (In order to turn it off, replace the ON keyword above with OFF). After the statement is turned on and queries are executed, the parse, compile & execution times are displayed in the messages tab next to the results tab of the query results. Here is an example: For the results above, we first see two “SQL Server Parse and Compile times”. For both of them in this example they are 0. The first result, relates to how long it took to compile the actual “EXEC” statement. The second parse and compile relates to how long it took to compile the entire procedure. Since it is 0, this means the stored procedure is using an existing execution plan, meaning it has already been compiled. The next two lines relate to the two statements that exist in the stored procedure.  The first took 123 ms, while the next took 210 ms.  The last statement is the overall time it took to execute the procedure. Overall, the SET STATISTICS TIME statement is […] Continue reading ...

Choosing a Rules Engine Design

For those that don’t know. A rules engine is a way to harness powerful decision-making based on source data. These decisions are defined in rules. Rules will generally consist of a query that outputs a list of successes or failures, depending on what needs to be reported on. In my experience, I have only found two major methods for building a rules-based engine in SQL Server. They both have their pros and cons. The trick is finding the middle ground which is defined as a third method between the two different extremes that will fit your requirement. With that said we will discuss the two extremes — and the middle ground. The Three Kinds of SQL Rules Engines Extreme 1 – The Dynamic SQL Rules Engine The first method involves dynamic SQL. Using dynamic SQL, the sky is the limit in regards to possibilities. The downfall however is maintainability. Dynamic SQL is very hard to maintain and even harder to troubleshoot and debug. My experience dealing with dynamic SQL is that it often starts out small, but once you’ve gone the dynamic route, it gets bigger and bigger and harder to maintain. Therefore I try to avoid this method altogether. If you’re into it, you’ll definitely find more job security, I’m not into that game personally. Extreme 2 – The Hard-coded Rules Engine The next method is to write each rule as a hardcoded procedure. When the rule executes, it returns back the list of results that fail (or pass) […]

Select Distinct

Select Distinct a way to tell SQL Server to not return duplicate rows. Adding the distinct keyword makes it an expensive query because it essentially performs a group by for all of the returned columns. That being the case, it should only optimally be used on queries that return a small number of rows, or small number of columns. When using Select Distinct, you should keep in mind that this is generally a workaround to either improper joining of tables, or an improper where clause that is not limiting the result set to only returning one instance of a record. In order to utilize distinct, all you need to do, is place it before the select list. [cc lang=”sql”] SELECT DISTINCT * FROM yourtable [/cc] This will surely only return a unique instance of every record in the result. Continue reading ...

SQL Server SPID – What is it?

A SPID in SQL Server is a Server Process ID. These process ID’s are essentially sessions in SQL Server. Everytime an application connects to SQL Server, a new connection (or SPID) is created. This connection has a defined scope and memory space and cannot interact with other SPIDs. The term SPID is synonymous with Connection, or Session. Viewing SPIDs In order to view all the connections in SQL Server execute the following query. [cc lang=”sql”] SELECT * FROM sys.dm_exec_sessions [/cc] From here we see a session_id shown in the left hand column. This is also known as the SPID. To find the SPID for your current execution window run this. [cc lang=”sql”] SELECT @@SPID [/cc] Connection Settings Each SPID can have it’s own connection settings. Connection settings can be defined by the connection string or the default values for the SQL Server instance, so if two SPIDs come in through the same connection string, then they will also have the same connection settings. This is important because execution plans that are generated for queries are dependent on the connection settings. If two different SPIDs come in with different connection settings, then they will not use the same execution plan defined for a SQL statement. In order to view the connections settings execute the SQL Statement shown above. Transaction Isolation SPIDs can have their own transaction isolation levels defined globally. When set within a connection, all proceeding executions maintain the same transaction isolation. For more on isolation levels, view this Temp […]