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

Tuesday, November 27, 2018

CASE / CONCATENATE a full name from multiple columns

Today a customer of mine was trying to query a user's full name out as one column, rather than as three separate columns, FirstName, MiddleName and LastName.  He was having problems with the users who did not have a MiddleName, because the NULLs were causing his concatention to come back empty.  This happens because by default, any string concatenation (+) that includes a NULL value will also produce a NULL result.  This post will show you two ways to work around that, using the AdventureWorks2012.Person.Person table.

Option #1 - CASE 
First I pull out the three columns separately, followed with my customer's statement and finishing with my CASE on the MiddleName.  The end result for records with no MiddleName is derived from First and Last name, rather than NULL:

    SELECT
       FirstName,MiddleName,LastName,
       FirstName + ' ' + MiddleName +' '+ LastName [NoGood],
    CASE 
       WHEN MiddleName IS NULL THEN FirstName + ' ' + LastName
       WHEN MiddleName IS NOT NULL THEN FirstName + ' ' + MiddleName + ' ' + 
        LastName END [FullName]
    FROM
        Person.Person;


Output:


Option #2 - CONCAT_NULL_YIELDS_NULL 
Even easier, you can use CONCAT_NULL_YIELDS_NULL to tell the server to treat the NULLs like empty strings instead of NULLs.  Run exactly the same query as above, but prefaced like this:
    
     SET CONCAT_NULL_YIELDS_NULL OFF; 
   SELECT
       FirstName,MiddleName,LastName,
       ........
       ....

Output:

Pretty snazzy.  BUT -- they say the CONCAT_NULL_YIELDS_NULL will always be ON in a future release, so this 2nd option is not a long-term solution.  See here for more details:



Can I change a normal SQL Server column to a computed column?

Nope.  Per BOL, "To change an existing column to a computed column you must drop and re-create the computed column."   Though because the computed column isn't there yet, I am fairly sure they meant  '...drop and re-create the existing column'.  See here:  Not going to happen

So what do you do?  Well, you could create a new table with computed columns and copy the data into it, OR you could rename your existing columns and create new ones with the intended computations.  I like the 2nd option a little better because I don't have to move a bunch of data around AND because I can see my old and computed columns side by side. Then, when I'm sure the computed columns are what I need -- I drop the old columns.  In this example I am adding 3 new computed columns to my CUSTOMER table.  The computations are based on other columns in the CUSTOMER table, but you see what I mean.  We rename the old column and define the new one w/the desired computation:


    -- rename old column
    EXEC sp_rename 'CUSTOMER.TSLONG', 'TSLONGold', 'COLUMN';
    -- add new column w/computation
    ALTER TABLE CUSTOMER ADD TSLONG AS (CONVERT(BIGINT,TS,0));

    -- rename old column
    EXEC sp_rename 'CUSTOMER.LOOKUPID', 'LOOKUPIDold', 'COLUMN';
    -- add new column w/computation
    ALTER TABLE CUSTOMER ADD LOOKUPID AS (CASE
      LEN(CUSTOMID) WHEN (0) THEN '8-'+CONVERT(NVARCHAR(20),SEQUENCEID,0) ELSE CUSTOMID END);

    -- rename old column
    EXEC sp_rename 'CUSTOMER.GENDER', 'GENDERold', 'COLUMN';
    -- add new column w/computation
    ALTER TABLE CUSTOMER ADD GENDER AS (CASE GENDERCODE
           WHEN (0) THEN 'Unknown'
           WHEN (1) THEN 'Female' END
           WHEN (2) THEN 'Male'
    );

Once you're sure everything is good, you'll use this to DROP the old columns:

       -- drop old columns
       ALTER TABLE CUSTOMER
       DROP COLUMN TSLONGold;
       ALTER TABLE CUSTOMER
       DROP COLUMN LOOKUPIDold;
       ALTER TABLE CUSTOMER
       DROP COLUMN GENDERold;

Or, you could even use this:

       ALTER TABLE CUSTOMER
       DROP COLUMN TSLONGold,LOOKUPIDold,GENDERold;

Did you know you drop more than one table column at a time?  How cool is that?!  Better be safe.


Hope you found this helpful.