Contact us

Madeira Data Solutions

Your Data, Our Solutions

Something Cool We Learned Today!

Written By: Guy Glantser 08/03/2018

 

We have many customers, and we work on lots of projects. Each project is different in so many ways: the business environment, the system architecture, the data platform, the people, the challenges…
This is why we get to learn so many new things on a daily basis, which is really awesome. Just think about the amount of knowledge that our team gains every day.
We believe in knowledge sharing, so we decided to do something about it. Each time one of us learns something cool, we write a short blog post about it and share it.
The goal of this blog post is to organize all those short blog posts in one place. We will update this blog post and add a new link every time a new post about “something cool I learned today” is published. So you can always come back here and learn something new.
And if you learned something cool today too, then please share it with us in the comments.

 

Happy learning!

 

SQL Server 2017

Can I pause or resume an index rebuild operation?

What is the Automatic Plan Correction in SQL Server 2017?

What is Graph Processing in SQL Server 2017?

 

Locks

What is the Role of the UPDATE Lock in SQL Server?

 

SQL Agent

Why is the job duration a negative number?

 

Useful Scripts

How to Update @@SERVERNAME to Actual Machine Name?

Creating AlwaysOn Availability Group With Scripts

 

Troubleshooting

How to troubleshoot database mail?

How to troubleshoot waiting tasks without requests in SQL Server?

What is the CXPACKET Wait Type?

 

Tuning

Which One is Better? IN or BETWEEN?

 

Database Management

How to Size Your Database Files?

3 responses to “Something Cool We Learned Today!”

  1. Guy Glantser says:

    Hi Aviad,

    It looks like you posted this comment in the wrong place. But anyway, thanks for the script.

    I wrote a different version of the script. I think it’s more simple and elegant:

    SELECT
    DatabaseId = UserDatabases.database_id ,
    LastAccessDateTime = MAX (LastOperations.LastAccessDateTime)
    FROM
    sys.databases AS UserDatabases
    LEFT OUTER JOIN
    sys.dm_db_index_usage_stats AS IndexUsageStats
    ON
    UserDatabases.database_id = IndexUsageStats.database_id
    CROSS APPLY
    (
    VALUES
    (IndexUsageStats.last_user_lookup) ,
    (IndexUsageStats.last_user_scan) ,
    (IndexUsageStats.last_user_seek) ,
    (IndexUsageStats.last_user_update)
    )
    AS
    LastOperations (LastAccessDateTime)
    WHERE
    UserDatabases.database_id > 4
    GROUP BY
    UserDatabases.database_id
    ORDER BY
    DatabaseId ASC;
    GO

    What do you think?

  2. Aviad Deri says:

    If you have instance with a lot of DB’s and you need to migrate it into new instance, but you want to upgrade only active DB’s, you can query the
    sys.dm_db_index_usage_stats view and check the last_user_seek/scan/lookup/update coloums (SQL2008R2 and newer only).
    this view shows data since last instance restart.
    you can use this on each DB you want to check:
    SELECT
    db_id() AS database_id,
    MAX(last_user_seek) AS last_user_seek,
    MAX(last_user_scan) AS last_user_scan,
    MAX(last_user_lookup) AS last_user_lookup,
    MAX(last_user_update) AS last_user_update
    FROM sys.dm_db_index_usage_stats u
    INNER JOIN sys.tables t ON t.object_id=u.object_id
    WHERE u.database_id=db_id()

    but…. you have instance with 300+ DB’s and it will take forever?
    use this:
    CREATE PROCEDURE [dbo].[sp_CollectDBUsage]
    AS
    IF OBJECT_ID(‘tempdb..#DBUsageStatus’) IS NOT NULL
    DROP TABLE #DBUsageStatus
    IF OBJECT_ID(‘tempdb..#DBUsageStatusStage’) IS NOT NULL
    DROP TABLE #DBUsageStatusStage
    CREATE TABLE #DBUsageStatus (
    DatabaseID INT,
    last_user_seek DATETIME,
    last_user_scan DATETIME,
    last_user_lookup DATETIME,
    last_user_update DATETIME
    )
    EXEC sp_msforeachdb ‘USE [?];
    INSERT INTO #DBUsageStatus
    SELECT
    db_id() AS database_id,
    MAX(last_user_seek) AS last_user_seek,
    MAX(last_user_scan) AS last_user_scan,
    MAX(last_user_lookup) AS last_user_lookup,
    MAX(last_user_update) AS last_user_update
    FROM sys.dm_db_index_usage_stats u
    INNER JOIN sys.tables t ON t.object_id=u.object_id
    WHERE u.database_id=db_id()’

    DECLARE @LastServerReboot DATETIME
    SELECT @LastServerReboot = login_time FROM master.sys.sysprocesses WHERE spid=1

    SELECT DB_NAME(DatabaseID) AS DatabaseName,
    CASE
    WHEN MAX(LastUserAccess) IS NULL THEN @LastServerReboot
    ELSE MAX(LastUserAccess)
    END AS LastUserAccess
    INTO #DBUsageStatusStage
    FROM (
    SELECT DatabaseID,last_user_seek AS LastUserAccess FROM #DBUsageStatus
    UNION
    SELECT DatabaseID,last_user_scan AS LastUserAccess FROM #DBUsageStatus
    UNION
    SELECT DatabaseID,last_user_lookup AS LastUserAccess FROM #DBUsageStatus
    UNION
    SELECT DatabaseID,last_user_update AS LastUserAccess FROM #DBUsageStatus
    ) LastUpdate
    WHERE DatabaseID > 4
    GROUP BY DatabaseID

    DROP TABLE #DBUsageStatus

    MERGE DBUsageStatus AS Target
    USING #DBUsageStatusStage AS Source
    ON Target.DatabaseName = Source.DatabaseName
    WHEN MATCHED AND Source.lastUserAccess>Target.lastUserAccess THEN
    UPDATE SET lastUserAccess=Source.lastUserAccess
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (DatabaseName, lastUserAccess) VALUES (Source.DatabaseName, Source.lastUserAccess)
    WHEN NOT MATCHED BY SOURCE THEN
    DELETE;
    and schedule it to run daily.

    I’m saving the data into my table to make sure i keep tracking the right DB usage even if the insatnce has been started.

    good luck with your migration 😉

Leave a Reply

Your email address will not be published. Required fields are marked *