Can we log all database user (session) specific data into table?

• fanindrabhortakke

  Old Hand

  Points: 349

  More actions

  July 27, 2016 at 9:06 am

  #315603

  Hi All,

  I am facing performace related issues for my .NET application that call several procedures. I have around 450 procedures for my application into database. I want to log their execution time in table for reporting purpose. as we have separate service user to access database so can we log this information using user's session ? please suggest or any alternative method.

  Thanks in advance,

• Lowell

  SSC Guru

  Points: 323504

  More actions

  July 27, 2016 at 9:59 am

  #1892334

  i use a variant of this to find procedures that take longer than 15 seconds, tune them one by one, and then lower my threshold until it's a diminishing return scenario(takes an hour of scripting and testing to save a few seconds);

  start with the slowest and work your way through them.

  this is getting the plans from the DMV's, for the CURRENT database, so get out of master, and in the db in question:

  DECLARE @seconds INT = 15;

  SELECT 'Purpose: Show Any queries taking longer than '

  + CONVERT(VARCHAR, @seconds) + ' seconds.' AS notes;

  DECLARE @ServerRestartedDate VARCHAR(30);

  SELECT @ServerRestartedDate = CONVERT(VARCHAR(30),dbz.create_date,120) FROM sys.databases dbz WHERE name='tempdb';

  --DECLARE @seconds INT = 15

  SELECT @ServerRestartedDate AS ServerRestartedDate,

  st.last_execution_time AS LastExecutionTime,

  st.execution_count AS TotalExecutions,

  (st.last_elapsed_time / 1000000 ) AS LastElapsedSeconds,

  (st.max_elapsed_time / 1000000 ) AS MaxElapsedSeconds,

  DB_NAME(fn.dbid) AS DBName,

  Object_schema_name(fn.objectid, fn.dbid) AS SchemaName,

  OBJECT_NAME(fn.objectid, fn.dbid) AS ObjectName,

  fn.*,

  st.*

  FROM sys.dm_exec_query_stats st

  CROSS APPLY sys.dm_exec_sql_text(st.[sql_handle]) fn

  WHERE 1=1

  AND st.last_elapsed_time >= ( 1000000 * @seconds ) --15 seconds default

  AND (DB_NAME(fn.dbid) =DB_NAME() )

  AND OBJECT_NAME(fn.objectid, fn.dbid) IS NOT NULL -- object name = proc/function name

  ORDER BY st.execution_count DESC;

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!