How I can find Longest Running Query and SPID on SQL Server 2008?
123207
Can anyone help me to find a below given details for long running query. ProcessID, process name, database, host ,user ,process login time, query start time and query duration.   I am looking for a query or an SP which gives me this data.
37214
Can I get the LAST INSERT ID() for a compound key in MySQL?
2551
Table:   UserId, Value, Date.   I want to get the UserId, Value for the max(Date) for each UserId. That is, the Value for each UserId that has the latest date. Is there a way to do this simply in SQL? (Preferably Oracle)  Update: Apologies for any ambiguity: I need to get ALL the UserIds. But for each UserId, only that row where that user has the latest date.
120659
Say I have the following query:  SELECT *  FROM AppDetails, AppTags  WHERE AppDetails.AppID = '1'    AND AppDetails.AppID = AppTags.AppID   Which gives the following results:  AppID    AppName        AppType    Tag 1        Application1   Utility    Test1 1        Application1   Utility    Test2 1        Application1   Utility    Test3   How can I modify the query to return something like this:  AppID    AppName        AppType    Tags 1        Application1   Utility    Test1,Test2,Test3
109787
I'd like to know if there is a way to send a notification on deadlock? If so what queries would be required. I understand that SQL Server takes care of deadlocks, I simply would like information on the queries involved.  I found the following to determine long-running queries:  SELECT      creation_time     ,last_execution_time     ,total_physical_reads     ,total_logical_reads     ,total_logical_writes     , execution_count     , total_worker_time     , total_elapsed_time     , total_elapsed_time / execution_count avg_elapsed_time     ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,     ((CASE statement_end_offset         WHEN -1 THEN DATALENGTH(st.text)         ELSE qs.statement_end_offset END     - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st   where total_elapsed_time &gt;= 300000000 --5 min ORDER BY total_elapsed_time / execution_count DESC;    I'd like to know if the above is the right way to go, or is there a better way to determine if any query takes longer than a specific interval say 5 min as shown?  Thanks
8946
I have some queries that are parameterized, but they are still creating a new execution plan each time. I am using SQL Server 2016.  Queries are like:  (@P1 varchar(1043),@P2 varchar(6)) UPDATE table  SET FILEDATA=@P1  WHERE FILEID=@P2   This query is not using the already generated execution plan from the cache, rather it is creating a new plan each time.
78722
I am trying to do an update on a table that has 4 million records. The main table is indexed on SerNum a unique field. I use a temp table to update from that also has a unique index on SerNum. I am told this should happen fast but it's taking hours. It's been running for 2 hours and if I add up the rows affected I get 5,260,000 which is weird because I only have 3,869,000 records in the table. The select into the temp table shows it has 3,390,954. Here is what I have so far can this be more efficient?   USE IMB_TraceData GO  IF OBJECT_ID('tempdb.dbo.PRIMARY KEY (lname,') IS NOT NULL     DROP TABLE #MaxDate; GO  -- ****************************************** -- Create Work Table -- ****************************************** SELECT * INTO #MaxDate FROM     (     SELECT  *     FROM    (             SELECT  *,              ROW_NUMBER() OVER (PARTITION BY TR_SerNum              ORDER BY TR_Scan_Date DESC) AS rn             FROM  Trace_Return_Table             ) a     WHERE   a.rn = 1     ) AS n GO CREATE INDEX IDX_SerNum ON #MaxDate(TR_SerNum) GO  DECLARE  @UPDATES TABLE (PK_ID int NOT NULL Primary KEY) DECLARE @x INT  SET @x = 1 SET ROWCOUNT 10000  WHILE @x &gt; 0     BEGIN         BEGIN TRANSACTION         UPDATE TOP (10000) IMB_TBO_Data.dbo.The_Big_One          SET TR_Scan_Date = n.TR_Scan_Date,             TR_Facility_Id = n.TR_Facility_Id,             TR_Operation_Code = n.TR_Operation_Code         OUTPUT inserted.HT_SerNum INTO @Updates         FROM IMB_TBO_Data.dbo.The_Big_One t         INNER JOIN #MaxDate n             ON t.HT_SerNum = n.TR_SerNum         WHERE NOT EXISTS          (SELECT * FROM @UPDATES AS Updates         WHERE UPDates.PK_ID = t.HT_SerNum)         SET @x = @@ROWCOUNT         COMMIT TRANSACTION     END  SET ROWCOUNT 0
9664
I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.
65876
I have a complex query which runs in 2 seconds in the query window, but about 5 minutes as a Stored Procedure. Why is it taking so much longer to run as a stored procedure?  Here's what my query looks like.   It takes a specific set of records (identified by @id and @createdDate), and a specific time frame (1 year starting from @startDate) and returns a summarized list of letters sent and estimated payments received as a result of those letters.  CREATE PROCEDURE MyStoredProcedure     @id int,     @createdDate varchar(20),     @startDate varchar(20)   AS SET NOCOUNT ON      -- Get the number of records * .7     -- Only want to return records containing letters that were sent on 70% or more of the records     DECLARE @limit int     SET @limit = IsNull((SELECT Count(*) FROM RecordsTable WITH (NOLOCK) WHERE ForeignKeyId = @id AND Created = @createdDate), 0) * .07      SELECT DateSent as [Date]          , LetterCode as [Letter Code]         , Count(*) as [Letters Sent]         , SUM(CASE WHEN IsNull(P.DatePaid, '1/1/1753') BETWEEN DateSent AND DateAdd(day, 30, DateSent) THEN IsNull(P.TotalPaid, 0) ELSE 0 END) as [Amount Paid]     INTO #tmpTable     FROM (          -- Letters Table. Filter for specific letters         SELECT DateAdd(day, datediff(day, 0, LR.DateProcessed), 0) as [DateSent] -- Drop time from datetime             , LR.LetterCode -- Letter Id             , M.RecordId -- Record Id         FROM LetterRequest as LR WITH (NOLOCK)         INNER JOIN RecordsTable as M WITH (NOLOCK) ON LR.RecordId = M.RecordId         WHERE ForeignKeyId = @id AND Received = @createdDate             AND LR.Deleted = 0 AND IsNull(LR.ErrorDescription, '') = ''             AND LR.DateProcessed BETWEEN @startDate AND DateAdd(year, 1, @startDate)             AND LR.LetterCode IN ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o')     ) as T     LEFT OUTER JOIN (          -- Payment Table. Payments that bounce are entered as a negative payment and are accounted for         SELECT PH.RecordId, PH.DatePaid, PH.TotalPaid         FROM PaymentHistory as PH WITH (NOLOCK)             INNER JOIN RecordsTable as M WITH (NOLOCK) ON PH.RecordId = M.RecordId             LEFT OUTER JOIN PaymentHistory as PR WITH (NOLOCK) ON PR.ReverseOfUId = PH.UID         WHERE PH.SomeString LIKE 'P_'              AND PR.UID is NULL              AND PH.DatePaid BETWEEN @startDate AND DateAdd(day, 30, DateAdd(year, 1, @startDate))             AND M.ForeignKeyId = @id AND M.Created = @createdDate     ) as P ON T.RecordId = P.RecordId      GROUP BY DateSent, LetterCode     --HAVING Count(*) &gt; @limit     ORDER BY DateSent, LetterCode      SELECT *     FROM #tmpTable     WHERE [Letters Sent] &gt; @limit      DROP TABLE #tmpTable   The end result looks like this:   Date        Letter Code     Letters Sent    Amount Paid 1/1/2012    a               1245            12345.67 1/1/2012    b               2301            1234.56 1/1/2012    c               1312            7894.45 1/1/2012    a               1455            2345.65 1/1/2012    c               3611            3213.21   I'm having problems figuring out where the slowdown is, because everything runs extremely fast in the query editor. It's only when I move the query to a stored procedure that it starts taking so long to run.   I'm sure it has something to do with the query execution plan getting generated, but I don't know enough about SQL to identify what could be causing the problem.  It should probably be noted that all the tables used in the query have millions of records.  Can someone explain to me why this is taking so much longer to run as a stored procedure than in the query editor, and help me identify what part of my query could be causing performance issues when run as a stored procedure?
110996
This next year, I am helping an effort to clean several SQL Server environments.    We have about 10,000 stored procedures and estimate that only about 1000 of them are used on a regular basis, and another 200 or so are used on a rare occasion, meaning we have a lot of work to do.    Since we have multiple departments and teams that can access these databases and procedures, we are not always the ones calling the procedures, meaning that we must determine what procedures are being called.  On top of that, we want to determine this over a few months, not in a few days (which eliminates some possibilities).  One approach to this is to use the SQL Server Profiler and track what procedures are being called and compare them to the list of what procedures we have, while marking whether the procedures are used or not.  From then, we could move the procedures to a different schema in case a department comes screaming.  Is using the Profiler the most effective approach here?  And/Or have any of you done something similar and found another way/better way to do this?
4884
My boss would like me to find which (of approx 400+) stored procedures are not being used by our website any more. I'm found and reworked a script to give me the details of most recent executions, however it only goes back as far as 10:17am September 1st.  My boss assures me that (although he likes to tinker with the server in his spare time) the server has been up since May 5th.  I'm pretty new to this, so explain it like I'm a particularly simple house pet, but is there are reason why my results table only goes back 1 month, instead of the full 6? I would assume that uptime is reset if the server is restarted, but I'm not sure why I wouldn't have any history that far back.  Does viewing the procedure by using right-click -&gt; modify count as an execution?  SQL Query  SELECT qt.text AS 'SP Name', qt.dbid as 'SP DB', qs.execution_count AS 'Execution Count', qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime', qs.total_worker_time AS 'TotalWorkerTime', qs.total_physical_reads AS 'PhysicalReads', qs.last_execution_time 'Last Executed' FROM sys.dm_exec_procedure_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt where qs.database_id = 5 ORDER BY qs.last_execution_time asc   My question relates more to the time frame of the results. I'm finding the information I need in the month of results I have but I'm worried there are 6 months of results that aren't being found found.  This returns May 5th:  select create_date from sys.databases where name = N'tempdb';   The following returns "2015-05-05 08:27:42.080":  SELECT sqlserver_start_time FROM sys.dm_os_sys_info;   Finally, this one returns "2013-09-15 19:34:34.667":  SELECT top 1 [rs].[destination_database_name], [rs].[restore_date]  FROM msdb..restorehistory rs  where destination_database_name = '%ID%'  ORDER BY [rs].[restore_date] DESC;
120183
How many records should there be before I consider indexing my sql tables?
70942
I have a huge database with 100's of tables and stored procedures. Using SQL Server 2005, how can I get a list of stored procedures that are doing an insert or update operation on a given table.
96949
I have a table with index (autoincrement) and integer value. The table is millions of rows long.  How can I search if a certain number appear in the last n rows of the table most efficiently?
99663
How can I set up a query to search for records that were modified in the last 15 minutes? This is being used in Jitterbit dataloader not using apex. From most posts I have seen this doesn't seem possible.  Another idea I had was use a formula that would mark a boolean field as true if record was modified in last 15 minutes. However, I had no luck with the formula either.  Does anyone have a workaround or solution?
10446
How do you return 1 value per row of the max of several columns:  TableName  [Number, Date1, Date2, Date3, Cost]   I need to return something like this:  [Number, Most_Recent_Date, Cost]   Query?
26688
I need to delete some rows from one large table. The rows to delete shouldn't be in another table, example:  DELETE FROM LargeTable WHERE id IS NOT IN (SELECT DISTINCT foreign_id from EvenLargerTable)  but my server can't handle such blunt query, because there are almost a million records in the LargeTable and few million records in the EvenLargerTable  How can I solve it?