THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tamarick Hill

  • July, the 31 Days of SQL Server DMO’s – Day 16 (sys.dm_sql_referenced_entities)

    The sys.dm_sql_referenced_entities Dynamic Management Function returns a result set of all objects that are being referenced as part of the definition of the object that you reference within the function argument section. You can pass in either a Server trigger, database trigger, or any object  such as a table or view. To better illustrate how this function works lets look at a stored procedure and a view in the AdventureWorks2012 database.

    If we script out the uspGetEmployeeManagers stored procedure we will see the code in the screenshot below.

    image

    If we needed to know all of the tables, columns, views, functions, etc. that are referenced in this stored procedure, we could easily just read through the code since its not too long.  But what if the code was 1000 lines worth of complex code? It would be difficult to gather a list of all dependent objects without running the risk of missing something. This is where the sys.dm_sql_referenced_entities function comes in handy. If we run a quick query against this function, we can immediately see the benefit:

    SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name,
           referenced_class_desc, is_ambiguous, is_selected, is_updated, is_select_all
    FROM sys.dm_sql_referenced_entities ('dbo.uspGetEmployeeManagers', 'OBJECT')

    image

    As we see from the result set, this function returned all of the objects that are referenced in the stored procedure. From the first 3 columns that I am selecting from this function, we can see the Schema name, Object name, and column name fields. The next column is the referenced class description which can either be an OBJECT OR COLUMN, TYPE, XML SCHEMA COLLECTION, or a PARTITION FUNCTION. The next column, is_ambiguous, describes whether or not the referenced object is ambiguous and can resolve to multiple different objects during run-time. The is_updated column describes whether or not this object is updated as part of the stored procedure. Lastly, the is_select_all column refers to whether or not a particular column is included as part of a select * statement.

    This DMV is very helpful when you need to identify all objects that are being referenced as part of another object.

    For more information about this DMV, see the below Books Online link:

    http://msdn.microsoft.com/en-us/library/bb677185.aspx

    Follow me on Twitter @PrimeTimeDBA

  • July, the 31 Days of SQL Server DMO’s – Day 15 (sys.dm_os_volume_stats)

     

    The sys.dm_os_volume_stats Dynamic Management Function is used to return information about disk volumes that contain your database files. This is a function so you have to pass in a database_id and a file_id. To better illustrate this function, I will run the below query on my TRAINING instance.

    SELECT db_name(vs.database_id) AS DatabaseName, vs.file_id, vs.volume_mount_point, vs.volume_id, vs.logical_volume_name,
           vs.file_system_type, (vs.total_bytes/1024/1024/1024) AS [TotalSize(GB)], (vs.available_bytes/1024/1024/1024) AS [AvailableSize(GB)],
           vs.supports_compression, vs.supports_alternate_streams, vs.supports_sparse_files, vs.is_read_only, vs.is_compressed
    FROM sys.master_files mf
    CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
    WHERE db_name(vs.database_id) like 'Adventure%'

     

    image

    image

    In the query above, I used the information from sys.master_files and then used the CROSS APPLY with my sys.dm_os_volume_stats function to return the shown result sets. In addition to this, I filtered the results so we only se information for the ‘AdventureWorks2012’ and ‘AdventureWorksDW2012’ databases.

    The first column returned by this function displays the Database Name. The second column displays the file id which in my case is the data file and transaction log file. The volume_mount_point column represents the the mount point that the volume is rooted to. Next we have the id for the specific volume and a name for the volume. The file_system_type column signifies whether you are using NTFS, FAT, RAW, etc system file types. The next columns represent the size of my volume and how much of that volume is still available for use. The next three columns of this function are self explanatory for the most part, signifying whether or not compression, alternate streams, or sparse files are supported. Lastly, we have have columns displaying whether or not the volume is read only or if the volume is compressed.

    Many DBA’s tend to rely on the extended stored procedure xp_fixeddrives when needing to see free size information about the various drives on a particular server. The problem with disk drive letters is that you are limited by the number of letters in the alphabet. If your database servers use mount points instead of drive letters, then xp_fixeddrives becomes useless to you at that point. This is when I find a Dynamic Management Function like this one to be extremely useful. There are other ways to get mount point information such as using powershell and WMI, but for a SQL Server solution, this DMF provides one of the best solutions available.

    For more information about this Dynamic Management Function, see the below Books Online link:

    http://msdn.microsoft.com/en-us/library/hh223223.aspx

    Follow me on Twitter @PrimeTimeDBA

  • July, the 31 Days of SQL Server DMO’s – Day 14 (sys.dm_os_cluster_nodes)

     

    The sys.dm_os_cluster_nodes DMV has been around since the 2005 version of SQL Server, but its result set was very limited up until 2012. In SQL Server 2005, 2008 and 2008R2, the DMV simply returns the Node Names for all of the servers that are part of a cluster. This one column in the only thing returned.

    In SQL Server 2012, the DMV has been expanded to include status and status_description columns, which tells us whether a particular node is “up”, “down”, “paused”, “joining”, or “unknown”. In addition, the 2012 version of this DMV also includes an ‘is_current_owner’ column to indicate which node is the current owner of the SQL Server cluster resource.

    This is a very simplistic DMV but returns useful information about the nodes making up your cluster and their current status.

    For more information about this DMV, please see the below Books Online link:

    http://msdn.microsoft.com/en-us/library/ms187341(v=sql.110).aspx

    Follow me on Twitter @PrimeTimeDBA

  • July, the 31 Days of SQL Server DMO’s – Day 13 (sys.dm_os_windows_info)

     

    The sys.dm_os_windows_info DMV is probably the most simplistic DMV that I will show you during this whole 31 days of DMV’s series. Generally I would not post something this short but lets be honest, it’s the weekend and I’m sure you all have better things to do than sit around and read my blog!  :)

    So with that being said, lets get into this. The sys.dm_os_windows_info DMV provides you basic information about the version of Windows that you SQL Server instance is currently running on. Lets run this query so we can see the results returned.

    SELECT * FROM sys.dm_os_windows_info

    image

    As you can see, only 4 columns are returned from this DMV. The columns are self-explanatory for the most part but just so we can say we went through them, the first column, ‘windows_release’, represents the Windows Operating System version number. Then second column ‘windows_service_pack_level’ is the current Windows Service Pack level that is installed on the server. The third column is the windows sku number and the fourth column is the identifier for the language of the operating system.

    This is a very basic DMV to return Operating System information.

    For more information on this DMV, see the below Books Online link:

    http://msdn.microsoft.com/en-us/library/hh204565.aspx

    Follow me on Twitter @PrimeTimeDBA

  • July, the 31 Days of SQL Server DMO’s – Day 12 (sys.dm_os_performance_counters)

     

    The sys.dm_os_performance_counters DMV returns information about SQL Server performance counters that are being collected on the server. The performance counters that you will find in this DMV are the SQL Server performance counters that you would normally find using Windows Performance Monitor.

    Lets have a quick look at this DMV to view the columns that are returned from this DMV.

    SELECT * FROM sys.dm_os_Performance_counters

    image

    The first column returned is the ‘object_name’ column which represents the category that the particular counter belongs to. The second column, ‘counter_name’, is the actual name of the counter. The third column, ‘instance_name’, is usually the database name that the counter is coming from, but sometimes can represent a more another category group name for certain performance counters that are instance scoped as opposed to database scope. The fourth column in the result set is ‘cntr_value’ which is the current value for a specific counter. The ‘cntr_type’ column is used to define the type of counter according to the Windows performance architecture. For many of the performance counters, this cntr_type column is necessary to properly calculate the value of a counter.

    This DMV provides useful information about practically every SQL Server performance counter out there. I use it occasionally for very specific counters I am interested in such as Logins/Sec, Page Reads/Sec, or Memory Grants Pending but for most of my performance counter needs, I will still rely on the Windows Performance Monitor tool. I am in no way implying that you should not use this DMV, me personally, I just find the Windows Performance monitor tool a little easier to work with for my needs. Once I become more skilled with how to properly use the ‘cntr_type’ column to properly calculate by counter value, then this DMV will probably be more beneficial to me.

    However, this is still a powerful and useful DMV that I felt you should at least know its out there.

    For more information about this DMV, please see the below Books Online link:

    http://msdn.microsoft.com/en-us/library/ms187743.aspx

    Follow me on Twitter @PrimeTimeDBA

  • July, the 31 Days of SQL Server DMO’s – Day 11 (sys.dm_os_wait_stats)

     

    The sys.dm_os_wait_stats DMV collects instance wide aggregations of all waits that have occurred on your SQL Instance since the last time the DMV statistics were reset. This DMV is useful when needed to determine the top waits that are occurring on your server so that you know where you should focus your tuning efforts.

    To help illustrate this DMV, let run a quick query against it:

    SELECT * FROM sys.dm_os_wait_stats WHERE waiting_tasks_count > 0

    image

    The first column, wait_type, displays each wait type that has occurred on your instance since the last time the DMV statistics were reset. The second column, waiting_tasks_count, give you the number of times that a particular wait type has occurred. The wait_time_ms column gives you the total time in milliseconds that a particular wait type has consumed. The max_wait_time_ms column gives you the maximum time in milliseconds that a wait type has waited for out of the X number of times it has waited. The final column returned by this DMV is the signal_wait_time_ms. This column represents the amount of time that a thread has spent on the Runnable queue. To help illustrate what this column represents, when a thread is executing, its status is listed as ‘Running’ and it is currently on a CPU executing. If the thread needs a resource that it cannot access yet, it will move to the Suspended Queue and have a status of ‘Suspended’. Once the thread acquires the resources it needs it moves to the Runnable queue and has a status of Runnable. This is when the signal_wait_time counter begins. The thread has all of the resources it needs and is simply waiting for its turn to get back onto the CPU. Once it moves back to the CPU, the status will change to ‘Running’.

    **Note: Threads can also come off of the CPU even if they are not lacking any needed resources. This happens when there are other threads in a ‘Runnable’ state waiting to get on the CPU. In this case the thread currently on the CPU will yield so that other Runnable processes are not constantly waiting for their chance on the CPU. This is generally represented by a SOS_SCHEDULER_YIELD wait type and can often indicate CPU pressure. When threads yield, they go directly to the ‘Runnable’ queue.

    As stated earlier, this DMV is very useful for tracking down the top waits on your server so you can formulate a plan for improving the server performance.

    For more information about this DMV, please see the below Books Online link:

    http://msdn.microsoft.com/en-us/library/ms179984.aspx

    Follow me on Twitter @PrimeTimeDBA

  • July, the 31 Days of SQL Server DMO’s – Day 10 (sys.dm_os_waiting_tasks)

     

    The sys.dm_os_waiting_tasks DMV is a very powerful DMV and one of my favorites. How many times have you been approached by a user saying “My query is taking too long to run, can you see what the problem is”? Or maybe you have heard the famous question “The server seems to be moving very slow today compared to normal, can you see what’s going on?” As a DBA, these are all very common questions and one of the first places I will generally check is the sys.dm_os_waiting_tasks DMV. This DMV provides information about tasks that are currently “waiting” on something within SQL and information about the type of resource that is being “waited” for.

    Lets have a quick look at this DMV to help illustrate the type of information it returns and how it can be used.

    SELECT * FROM sys.dm_os_waiting_tasks

    image

    The first column presented is the waiting_task_address which represents the internal memory address for the task that is waiting. Next in the result set is the session_id (SPID) and the exec_context_id. Assuming that no explanation is needed for the session_id, I will go directly to the exec_context_id which represents the ID of the thread from the session_id whenever you have parallelism taking place. If a particular session_id is not running in parallel then you will always see a 0 for the exec_context_id field. The next field is the wait_duration_ms which gives you the time in milliseconds that a particular SPID has been waiting.

    The next field is the crown jewel of this DMV which is the actual wait type. Looking at the screenshot above we see various wait types. For example we see ‘LAZYWRITER_SLEEP’ which is a background process in SQL Server that occurs when the lazy writer is waiting for more work. We see the wait type ‘CXPACKET’ wait which simply means that this session is running in parallel and the current session_id/exec_context_id is waiting for some of the other threads to complete their work. One of the more common wait types, although not displayed in the screen shot above, is the ‘LCK_M_%’ wait type. This wait type indicates that the current session is currently waiting on a lock and based on the type and mode of the lock it is waiting for, the wait type will be changed accordingly. For example, the ‘LCK_M_IS’ wait type indicates an Intent Shared lock is being waiting on. The ‘LCK_M_X’ indicates that an Exclusive lock is being waited for.

    I wont go into detail about what every single wait type is, but Tom Davidson created a great white paper on SQL Server 2005 Waits and Queues which I highly recommend. Although the white paper was written for SQL Server 2005, the information within it still applies for current version. You can find the white paper at https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&ved=0CDYQFjAA&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2F4%2F7%2Fa%2F47a548b9-249e-484c-abd7-29f31282b04d%2FPerformance_Tuning_Waits_Queues.doc&ei=SD3cUfv0KYvI9gS5ooHgDQ&usg=AFQjCNFRIebSlMLnry8gH99CQklhdmokJw&bvm=bv.48705608,d.eWU

    This DMV also returns information about the session_id that may be causing blocking and its resource addresses for that particular blocking session.

    All in all, this is a very powerful DMV, and as I said before, one of my favorites. I would highly recommend that you play around with this DMV, get familiar with some of the more common wait types, and add this tool to your troubleshooting toolkit.

    For more information about this DMV, see the below Books Online link:

    http://msdn.microsoft.com/en-us/library/ms188743.aspx

    Follow me on Twitter @PrimeTimeDBA

  • July, the 31 Days of SQL Server DMO’s – Day 9 (sys.dm_os_sys_info)

     

    If you have been keeping up with this Dynamic Management Object series, you will have noticed that all of the previous DMO’s we have reviewed have come from the ‘exec’ namespace. Well today we are going to begin learning about some of the DMO’s in the ‘os’ namespace. The first one we will review is the sys.dm_os_sys_info DMV. This DMV returns information about the Operating System (OS) that SQL Server is running on and the resources that are available to SQL.

    To help illustrate the power of this DMV, I will execute it against my Training instance containing my AdventureWorks2012 database.

    SELECT * FROM sys.dm_os_sys_info

    image

    image

    image

    As you can see, there is a wealth of OS level information that is returned by this DMV. Since there are so many columns returned, I wont go into detail about each and every one of them, but I will touch on a few of them briefly.

    Two columns I find useful when I am trying learn more about my SQL Server systems, maybe after taking over a new system or starting a new job, is the CPU_Count and Hyperthread_Ratio columns. These columns represent the number of logical CPU’s on the system and the ratio of logical/physical cores per processor respectively.

    The next columns I find to be very useful are the physical_memory_kb, virtual_memory_kb, committed_kb, and committed_target_kb. These columns are beneficial when you are analyzing your memory consumption or memory requirements for SQL Server.

    For those of your that are into the Internals of SQL Server, you may find some benefit in the max_workers_count and scheduler_count columns. I have some DMV’s scheduled for a future day in this series that will deal with these Internals in more detail, so i wont go into detail about these now.

    Lastly the sqlserver_start_time column is a quick and easy way to view the last time that SQL Server was started. Have a look at this DMV for yourself against your systems and view the OS information that is returned.

    For more information about this Dynamic Management View, please see the below Books Online link:

    http://msdn.microsoft.com/en-us/library/ms175048.aspx

    Follow me on twitter @PrimeTimeDBA

  • July, the 31 Days of SQL Server DMO’s – Day 8 (sys.dm_exec_trigger_stats)

     

    The sys.dm_exec_trigger_stats Dynamic Management View is very similar to the sys.dm_exec_procedure stats Dynamic Management View that we viewed on yesterday (Day 7). This DMV is used to return performance statistics about triggers that have been cached on your SQL Server Instance. To illustrate the usage of this DMV, we will run a query against our AdventureWorks2012 database and view the result set.

    SELECT * FROM sys.dm_exec_trigger_stats
    WHERE database_id = db_id('AdventureWorks2012')

    image

    image

    image

    By looking at the results of this DMV, the first thing we notice, if you have been keeping up with this DMV series, is that it returns the exam same columns as the sys.dm_exec_procedure_stats DMV, except for triggers. As a result the same useful information can be gathered from this DMV about your trigger performance.

    From a performance perspective, we are able to view statistics about the min, max, last, and total worker time a trigger used, which represents the amount of CPU time in microseconds that this trigger consumed.

    We are able to see min, max, last, and total statistics for logical reads/writes and physical reads/writes. The duration that the trigger took to execute is also presented in the result set of this DMV represented as the min, max, last, and total elapsed times.

    When you need to analyze the performance of a trigger on your system, this is a good place to begin your analysis.

    For more information abut this Dynamic Management View, please see the below Books Online link:

    http://msdn.microsoft.com/en-us/library/cc280646.aspx

    Follow Me on Twitter @PrimeTimeDBA

  • July, the 31 Days of SQL Server DMO’s – Day 7 (sys.dm_exec_procedure_stats)

     

    The sys.dm_exec_procedure_stats is one of my favorite DMV’s that I will use when I am looking into the performance of a stored procedure. This DMV returns information about the performance of a cached stored procedures. The information within this DMV is aggregated since the last time that the DMV statistics were reset. To better illustrate the benefits of this DMV, let run a quick query against my AdventureWorks2012 database.

    SELECT * FROM sys.dm_exec_procedure_stats
    WHERE database_id = db_id('AdventureWorks2012')

    image

    image

    image

    By looking at the results returned by this DMV, we can gather a wealth of knowledge about how a stored procedure may be performing. We can easily obtain the name of the stored procedure by using the OBJECT_NAME function. This query also returns the sql_handle and plan_handle and if you have been keeping up with this month long series on Dynamic Management Objects, then you know how we can use these two columns to obtain the actual sql text and the execution plan for these stored procedures. We are also able to see when the stored procedure was added to the cache and the last time it executed as well as how many times it has executed.

    From a performance perspective, we are able to view statistics about the min, max, last, and total worker time a stored procedure used, which represents the amount of CPU time in microseconds that this stored procedure consumed.

    We are able to see min, max, last, and total statistics for logical reads/writes and physical reads/writes. The duration that the stored procedure took to execute is also presented in the result set of this DMV represented as the min, max, last, and total elapsed times.

    When looking for stored procedures that may need optimizations, this is an excellent place to start. Especially when you are needing a historical view and not necessarily a look at currently executing stored procedures. This is definitely one of my favorites!!

    For more information of this Dynamic Management View, please see the below Books Online link:

    http://msdn.microsoft.com/en-us/library/cc280701.aspx

  • July, the 31 Days of SQL Server DMO’s – Day 6 (sys.dm_exec_cached_plans)

     

    The sys.dm_exec_cached_plans DMV can be used whenever you want to get a quick look at each query plan that is currently being cached without your SQL Server instance. Using this DMV you are able to determine the amount of space that a particular query plan is consuming, how many objects are referencing a particular plan, and how many times a particular plan has been used.

    To further illustrate this DMV, lets run a quick query against it and review the results.

    SELECT * FROM sys.dm_exec_cached_plans

    image

    The bucketid column represents the hash bucket that contains the cached plan. The refcounts column represents the number of cache objects that are referening this particular plan. The usecounts column represent the number of times that this plan has been looked up.

    This DMV can also be used with a CROSS APPLY of the sys.dm_exec_sql_text function to return the batch text for these cached plans.

    SELECT cp.refcounts, cp.usecounts, cp.size_in_bytes, cp.objtype, st.text
    FROM sys.dm_exec_cached_plans cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

    image

    If you are doing some detailed analysis of memory consumption or query plan usage, this DMV can prove very beneficial to you.

    For more information on this Dynamic Management View, please see the below Books Online link:

    http://msdn.microsoft.com/en-us/library/ms187404.aspx

  • July, the 31 Days of SQL Server DMO’s – Day 5 (sys.dm_exec_query_plan)

     

    The sys.dm_exec_query_plan Dynamic Management Function is very similar to the sys.dm_exec_sql_text function that we reviewed on yesterday, except this function is used to return execution plans. To be exact, it returns either the cached or currently executing plan in Showplan XML format. Instead of passing in a ‘sql_handle’ as we did for sys.dm_exec_sql_text function, we will pass in a ‘plan_handle’ which can also be obtained by using the sys.dm_exec_requests DMV that we reviewed on Day 1.

    To illustrate how this function works, I will first need to capture a plan_handle. **NOTE: I have a SQL Statement running in a loop in a separate window against the AdventureWorks2012 database.

    SELECT session_id, command, sql_handle, plan_handle FROM sys.dm_exec_requests
    WHERE session_id >= 50 AND session_id != @@SPID

    image

    Now that I have a plan_handle to work with, I can either call the sys.dm_exec_query_plan function directly and pass in the plan_handle, or I can run a query against my sys.dm_exec_requests DMV and use a CROSS APPLY with the sys.dm_exec_query_plan function.

    SELECT * FROM
    sys.dm_exec_query_plan(0x06000500F96E970BB00FB9FC0200000001000000000000000000000000000000000000000000000000000000)

    image

    From running the above query, you will see that we get similar results as we did when using sys.dm_exec_sql_text, so I will not go into detail about what these columns represent. But the last column in our result set (query_plan) provides us with an XML link.  (Note: The query I had executing was not the most efficient query, but I wanted to provide an execution plan with several different operators listed)

    If we click on the link, we will see the following:

    image

    As I mentioned earlier, you can also use the CROSS APPLY to use this sys.dm_exec_query_plan function against every row returned from your call to sys.dm_exec_requests:

    SELECT er.session_id, er.status, er.command, er.plan_handle, qp.query_plan
    FROM sys.dm_exec_requests er
    CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp

    image

    This is a very fast and efficient way of finding execution plans for queries that are currently executing on your server. In a future post, we will review how to pull execution plans that may not be currently running, but exist in the cache.

    For more information on this Dynamic Management Function, please see the below Books Online link:

    http://msdn.microsoft.com/en-us/library/ms189747.aspx

  • July, the 31 Days of SQL Server DMO’s – Day 4 (sys.dm_exec_sql_text)

     

    The sys.dm_exec_sql_text Dynamic Management Object is the first Function that we will review. This function is used to return sql text from a batch by passing in the sql_handle. If we recall one of the DMV’s that we have previously reviewed (sys.dm_exec_requests), this DMV provided a sql_handle column for each of the requests that were executing on the server. If we take that sql_handle and pass it into the sys.dm_exec_sql_text function, we are able to extract the actual T-SQL statements that are being executed.

    To illustrate how this function works, I will first need to capture sql_handle. **NOTE: I have a SQL Statement running in a loop in a separate window against the AdventureWorks2012 Database.

    SELECT * FROM sys.dm_exec_requests
    WHERE session_id > 50 and session_id <> @@SPID

    image

    Now that I have a sql_handle to work with, I can either call the sys.dm_exec_sql_text function directly and pass in the sql_handle, or I can run a query against my sys.dm_exec_requests DMV and use a CROSS APPLY with the sys.dm_exec_sql_text function.

    SELECT * FROM sys.dm_exec_sql_text(0x0200000059598407105BDB167364C21EC3C2568FC10FF80A0000000000000000000000000000000000000000)

    image

    As we can see if the text column, the actual SQL text that I had running in a separate window is displayed here. Additionally you see columns for the Database ID and Object ID. These columns return NULL values whenever you are running ad hoc or perpared SQL Statements, which explains why they are NULL now. The ‘number’ columns refers to numbered stored procedures and the encrypted column represents whether or not the SQL text is encrypted or not.

    Optionally we could have queried the sys.dm_exec_requests DMV and used a CROSS APPLY as you see below.

    SELECT er.session_id, er.status, er.command, db_name(er.database_id) [Database], st.text
    FROM sys.dm_exec_requests er
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
    WHERE er.session_id > 50 and session_id <> @@SPID

    image

    This function is very useful when you need to extract the actual text that is being executed on a server. Many DBA’s tend to use DBCC INPUTBUFFER() but for extremely long SQL statements, the text output can be truncated when using DBCC INPUTBUFFER(). This Dynamic Management Function will never truncate your text.

    For more information on this DMF, please see the below Books Online link:

    http://msdn.microsoft.com/en-us/library/ms181929.aspx

  • July, the 31 Days of SQL Server DMO’s – Day 3 (sys.dm_exec_connections)

     

    The third DMV we will review is the sys.dm_exec_connections DMV. This DMV is Server-Scoped and displays information about each and every current connection on your SQL Server Instance. Lets take a look at some information that this DMV returns.

    SELECT * FROM sys.dm_exec_connections

    image

    image

    After reviewing this DMV, in my opinion, its not a whole lot of useful information returned from this DMV from a monitoring or troubleshooting standpoint. The primary use case I have for this DMV is when I need to get a quick count of how many connections I have on one of my SQL Server boxes. For this purpose a quick SELECT COUNT(*) satisfies my need. However, for those who need it, there is other information such as what type of authentication a specific connection is using, network packet size, and client/local TCP ports being used. This information can come in handy for specific scenarios but you probably wont need it very much for your day to day monitoring/troubleshooting needs. However, this is still an important DMV that you should be aware of in the event that you need it.

    For more information on this DMV, please see the below Books Online link:

    http://msdn.microsoft.com/en-us/library/ms181509.aspx

  • July, the 31 Days of SQL Server DMO’s – Day 2 (sys.dm_exec_sessions)

     

    This sys.dm_exec_sessions DMV is another Server-Scoped DMV which returns information for each authenticated session that is running on your SQL Server box. Lets take a look at some of the information that this DMV returns.

    SELECT * FROM sys.dm_exec_sessions

    image

    image

    image

    This DMV is very similar to the DMV we reviewed yesterday, sys.dm_exec_requests, and returns some of the same information such as reads, writes, and status for a given session_id (SPID). But this DMV returns additional information such as the Host name of the machine that owns the SPID, the program that is being used to connect to SQL Server, and the Client interface name. In addition to this information, this DMV also provides useful information on session level settings that may be on or off such as quoted identifier, arithabort, ansi padding, ansi nulls, etc. This DMV will also provide information about what specific isolation level the session is executing under and if the default deadlock priority for your SPID has been changed from the default. Lastly, this DMV provides you with an Original Login Name, which comes in handy whenever you have some type of context switching taking place due to an ‘EXECUTE AS’ statement being used and you need to identify the original login that started a session.

    For more information on this DMV, please see the below Books Online link:

    http://msdn.microsoft.com/en-us/library/ms176013.aspx

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement