Thursday, January 24, 2019

Who changed SQL server's max memory?

I found the max server memory (mb) was misconfigured today on one of my customer's servers.  The server had 32GB physical ram, but SQL Server's max server memory (mb) was set too high at 48GB.  The customer insisted they hadn't changed it, and asked me to find out who had... but how?  Easy peasy.  Using sp_readerrorlog we can search all of the available ERROR LOGs for the 'configuration option' string.  But, if it's not in the available ERROR LOGs, you'll see this:


In that case, we can got to go to the Windows Application Event log and search for the same thing.  Open Event Viewer, Windows Logs, right click Application and choose 'Find'.  Input your string and lookie there!  We found the configuration change string, and can see it was made TWO YEARS ago.  Have to wonder how that misconfiguration has impacted the server memory all this time!  



So, I changed it to 29GB, which is the recommended setting for a dedicated server with 32GB physical, and now we can see it in the current ERROR LOG.









Need to be careful about who is authorized to make changes like this.  And, we have to recognize that people make mistakes.  May be a good idea may be to trigger notifications when configuration changes are made.  Good idea!  ... for a later post.


Sunday, January 20, 2019

Who shrunk that database file?

Much like 'Who dropped that table?', you hope you're the only one shrinking files in your SQL Server database... but if you believe that, then I've got a bridge to sell you.  😏  Again, I really love SQL Server's default trace!  Incredibly helpful, with so many different uses!!  Here's an example of how to use it to find out who shrank the SQL Server database files.

First run this to find your trace file:

    -- find your default trace file
    SELECT * FROM fn_trace_getinfo(default);

Now input your filepath and name from the above query into the FROM in this statement:

    -- query it for any shrinkfiles
    SELECT
      TextData,
      HostName,
      ApplicationName,
      LoginName,
      StartTime 
   FROM
      [fn_trace_gettable]('C:\Program Files\Microsoft ...\log_175.trc', DEFAULT)
   WHERE 
      TextData LIKE 'DBCC SHRINKFILE%'

And here you go:


Now you know which files Mr/Ms Lenovo shrank, and when.  

Shrinking SQL Server's database files is something you'll do only in worst case scenarios.  ie., Your log is blowing up and you are going to run out of space in the middle of the production day.  Ok.  Shrink it.  But you should really try not to make this a repeat activity.  For more evidence of why, take a look at the Hamster Wheel of Death explanation from Brent Ozar.

Of course, as a reminder, if your default trace isn't enabled/running, it really should be.  Run this to confirm default trace status:

       SELECT * FROM sys.configurations WHERE configuration_id = 1568



Use REPLACE multiple times on a single column

Using REPLACE in a statement allows us to update, or 'replace' something from a string with something else, like this:

       DECLARE @sample VARCHAR(15);
    SET @sample = 'Mister Jones'
    SELECT @sample [Old], REPLACE(@sample,'Mister','Mr.') [New];

Will give this back to you:



But what if there is more than one string in a single data value that you want to replace?  Maybe you need to replace 'Mister' and 'Misses' from the same column -- or even Miz!  You can run REPLACE more than once on a single column, in a single statement, simply by nesting them.  Like this:
DROP TABLE #temp;
CREATE TABLE #temp (DataValue VARCHAR(55))
INSERT #temp (DataValue)
VALUES ('Mister Jones'),
          ('missus Smith'),
          ('Miz Wilson');

SELECT
       DataValue [Old],
       REPLACE(
              REPLACE(
                     REPLACE(
                     DataValue,'Mister','Mr.'),
              'missus','Mrs.'),
       'Miz','Ms.') [New]
FROM #temp;

Gives you back a little something like this:




Important to note that this is not without overhead.  Putting the data in clean, or correcting the data values with an UPDATE would be more effective, but I understand we don't have a choice sometimes.  The nested REPLACE will let you run more than one REPLACE on a single data column.

See here for more details on REPLACE.   



Wednesday, January 2, 2019

Query SQL Server transactions - per day, hour and minute

This post will help you query your SQL Server for transaction count at both the server level and for each of the databases.  The query returns transaction statistics per day, hour and minute since the time of the last service restart.


/* SQL Server transactions per day/hour/minute using sys.dm_os_performance_counters */

       -- declarations
       DECLARE      
              @Days SMALLINT,
              @Hours INT,
              @Minutes BIGINT,
              @LastRestart DATETIME;

       -- get last restart date
       SELECT 
              @Days = DATEDIFF(D, create_date, GETDATE()),@LastRestart = create_date
       FROM   
              sys.databases
       WHERE  
              database_id = 2;

       -- collect days/hours since last restart
       SELECT @Days = CASE WHEN @Days = 0 THEN 1 ELSE @Days END;
       SELECT @Hours = @Days * 24;
       SELECT @Minutes = @Hours * 60;


       -- trans since last restart
       SELECT 
              @LastRestart [LastRestart],
              @@servername [Instance],
              cntr_value [TotalTransSinceLastRestart],
              cntr_value / @Days   [AvgTransPerDay],
              cntr_value / @Hours  [AvgTransPerHour],
              cntr_value / @Minutes [AvgTransPerMinute]
       FROM
              sys.dm_os_performance_counters
       WHERE  
              counter_name = 'Transactions/sec'
              AND instance_name = '_Total';


       -- trans since last restart per database
       SELECT 
              @LastRestart [LastRestart],
              @@servername [Instance],
              instance_name [Database_Name],
              cntr_value [TotalTransSinceLastRestart],
              cntr_value / @Days [AvgTransPerDay],
              cntr_value / @Hours  [AvgTransPerHour],
              cntr_value / @Minutes [AvgTransPerMinute]
       FROM  
              sys.dm_os_performance_counters
       WHERE  
              counter_name = 'Transactions/sec'
              AND instance_name <> '_Total'
       ORDER BY
              cntr_value DESC;


Your output will be similar to this:

















Take a look at this for more details on the dm_os_performance_counters DMV:     sys.dm_os_performance_counters