Thursday, June 11, 2015

Query Open/High/Low/Close Tick Price data from SQL Server

I got a call from one of my prop trader customers today;  They wanted to pull back the tick prices in 5 and 10 minute bars, showing the OHLC in each interval. That means returning the Open, High, Low and Close prices for each 'bar'. Time is the only consideration for this chart; volume and trading activity have no bearing. In periods of high market activity, there would be more bars than in periods of low market activity. This type of collection allows traders to analyze price movement and market volatility.  

This is an example of the logic I built for them, trimmed down only for AAPL. I have coded it for any interval they choose;  5 and 10 minute intervals, or 1, 2 or 3 minutes, etc. See here:

     -- create and load @tickdata
    DECLARE @TickData AS Table ( 
TimeStamp DATETIME,
Price MONEY,
Symbol VARCHAR(5)
    )

    INSERT @TickData VALUES 
('2015-06-09 09:15:01',22.11,'AAPL'),
('2015-06-09 09:15:02',22.10,'AAPL'),
('2015-06-09 09:15:04',22.25,'AAPL'),
('2015-06-09 09:15:58',22.45,'AAPL'), 
('2015-06-09 09:16:03',23.12,'AAPL'), 
('2015-06-09 09:16:08',23.15,'AAPL'), 
('2015-06-09 09:17:10',22.28,'AAPL'), 
('2015-06-09 09:17:30',22.13,'AAPL'), 
('2015-06-09 09:18:59',22.15,'AAPL'),
('2015-06-09 09:18:02',20.19,'AAPL'), 
('2015-06-09 09:18:04',22.25,'AAPL'), 
('2015-06-09 09:20:58',24.14,'AAPL'), 
('2015-06-09 09:20:03',22.22,'AAPL'), 
('2015-06-09 09:21:08',22.28,'AAPL'), 
('2015-06-09 09:24:10',22.29,'AAPL'), 
('2015-06-09 09:25:30',22.13,'AAPL'), 
('2015-06-09 09:27:37',22.10,'AAPL'), 
('2015-06-09 09:27:40',23.25,'AAPL'), 
('2015-06-09 09:28:43',22.45,'AAPL'), 
('2015-06-09 09:28:50',22.22,'AAPL'), 
('2015-06-09 09:28:52',23.25,'AAPL'), 
('2015-06-09 09:29:53',22.28,'AAPL'), 
('2015-06-09 09:30:54',20.13,'AAPL'), 
('2015-06-09 09:30:56',21.11,'AAPL'), 
('2015-06-09 09:30:59',22.25,'AAPL'), 
('2015-06-09 09:31:02',22.45,'AAPL'), 
('2015-06-09 09:31:03',22.22,'AAPL'), 
('2015-06-09 09:32:07',22.25,'AAPL'), 
('2015-06-09 09:32:11',22.28,'AAPL'), 
('2015-06-09 09:34:12',22.13,'AAPL'), 
('2015-06-09 09:35:15',22.10,'AAPL'),
('2015-06-09 09:35:16',22.25,'AAPL'), 
('2015-06-09 09:36:18',22.45,'AAPL'), 
('2015-06-09 09:37:20',22.22,'AAPL'), 
('2015-06-09 09:37:25',22.25,'AAPL'), 
('2015-06-09 09:40:10',22.28,'AAPL'), 
('2015-06-09 09:40:11',22.13,'AAPL'); 

     -- pull it back out at intervals for the given @TimeInterval
     DECLARE @TimeInterval AS INT
     SET @TimeInterval= 10 -- minutes, 1 = 1 min, 5 = 5 min, etc.

     SELECT
Symbol,
DATEADD(mi,DATEPART(hh,TimeStamp)*60+    
           (DATEPART(mi,TimeStamp)/@TimeInterval) * @TimeInterval, CONVERT(Datetime,             CONVERT(varchar,TimeStamp,102))) [Time],
  (SELECT PRICE FROM @TickData WHERE TimeStamp = MIN(t.TimeStamp)) [Open],
MAX(Price) [High],
MIN(Price) [Low],
(SELECT PRICE FROM @TickData WHERE TimeStamp = MAX(t.TimeStamp)) [Close]
     FROM 
@TickData t
     GROUP BY 
Symbol, 
DATEADD(mi,DATEPART(hh,TimeStamp)*60+    
           (DATEPART(mi,TimeStamp)/@TimeInterval) * @TimeInterval, CONVERT(Datetime,             CONVERT(varchar,TimeStamp,102)));

I've run it for 2, 5 and 10 minute bars, and posted the output below for each. Take a look at that 2 minute bar; there are two spots with a larger than 2 minute jump. This is because it's coded to bring data back only for the bars where price data exists. Try it out on your own data, let me know what you think.

   2 minute bars -  
  

  5 minute bars -   










10 minute bars -  

Tuesday, June 9, 2015

SQL Server Login Properties

Just a quick post that you can use to check your SQL Server login properties.  Very helpful to keep an eye on the password policies and login state.  It's also something that you can use to see how many failed logins are occurring, and when. 

/* login properties */
SELECT
name [SQLLogin],
CASE WHEN is_policy_checked = 0 THEN 'Disabled'
                WHEN is_policy_checked = 1 THEN 'Enabled' END [EnforcePasswordPolicy],
CASE WHEN is_expiration_checked = 0 THEN 'Disabled' 
              WHEN is_expiration_checked = 1 THEN 'Enabled' END [EnforcePasswordExpiration],
CASE LOGINPROPERTY(name, 'IsLocked')
              WHEN 0 THEN 'No'
              WHEN 1 THEN 'Yes' ELSE 'Unknown' END [IsLocked],
LOGINPROPERTY(name, 'PasswordLastSetTime') [DatePasswordSet],
LOGINPROPERTY(name, 'BadPasswordCount') [FailedLogins],
LOGINPROPERTY(name, 'BadPasswordTime') [LastFailedLogin],
LOGINPROPERTY(name, 'LockoutTime') [DateLockedOut],
CASE LOGINPROPERTY(name, 'IsExpired')
             WHEN 0 THEN 'Password is not expired'
             WHEN 1 THEN 'Password is not expired, change it' ELSE 'Unknown' END [PwdExpired],
CASE LOGINPROPERTY(name, 'IsMustChange')
WHEN 0 THEN 'Must not change password at next login'
WHEN 1 THEN 'Must change password at next login' ELSE 'Unknown' END [PwdChangeNextLogin],
LOGINPROPERTY(name, 'DaysUntilExpiration') [DaysUntilPwdExpires]
FROM 
sys.sql_logins
WHERE 
[name] NOT LIKE '##%'
ORDER BY 
[name]


Take a look at this reference for more detailed LOGINPROPERTY information from MSFT:   
     https://msdn.microsoft.com/en-us/library/ms345412(v=sql.110).aspx
           

Sunday, June 7, 2015

Find last modified date for SQL Server database objects

Change control is for the database objects just as much as it is for the application layer. You'd like to think that everybody has a change control process in place, but that is not always the case. There are several third party tools, such as Apex, Red Gate and Quest Software, or there is even the SQL Server Audit tool , which happens to be one of my favorites. The Audit, however, is a much larger deployment, and requires quite a bit more due diligence to put together. When I have a chance, I will put a nice overview on the Audit for your review. Until then, this is just a quick script that you can use to see when an object was changed.   
        /* find last modified date per object */
        SELECT
        name [ObjectName],
        CASE WHEN type = 'U' THEN 'Table'
           WHEN type = 'P' THEN 'Procedure'
           WHEN type IN('AF','FN','FS','FT','IF','TF') THEN 'Function'
           WHEN type = 'V' THEN 'View'
           WHEN type = 'C' THEN 'CheckConstraint'
           WHEN type = 'D' THEN 'DefaultConstraint'
           WHEN type = 'PK' THEN 'PrimaryKeyConstraint'
           WHEN type = 'F' THEN 'ForeignKeyConstraint'
           WHEN type = 'UQ' THEN 'UniqueConstraint'
           WHEN type = 'SN' THEN 'Synonym'
           WHEN type = 'TR' THEN 'Trigger' END [ObjectType],
        modify_date [Modified]
        FROM
        sys.objects
        WHERE 
        is_ms_shipped = 0  -- exclude system objects
        ORDER BY
       [Modified] DESC


I ran it against AdventureWorks2012, this is just a snapshot of the results:



Friday, June 5, 2015

Rename SQL Server Database, Table, Column, Index or Constraint

I can't count the number of times over the years that I have referenced BOL, or done a quick google, checking the syntax for my renames.  Not just constraints or indexes, but tables and columns, too.  Even databases!  In this post I am going to to give you several useful examples, compatible with all versions from v2000 to v2014. 

Side note;  In most cases you can perform these renames in the GUI, but I am a huge advocate of NOT doing renames through the GUI. The performance goes to the tubes very quickly, and you won't have near enough control over the system when these manipulations are occurring.  Don't use the GUI.  Learn and master the tSQL. You won't regret it.


    #1  Rename Database
    v2000 
    EXEC sp_renamedb @dbname = 'OldDatabaseName', @newname = 'NewDatabaseName' 

    v2005 - v2014     
    ALTER DATABASE OldDatabaseName MODIFY NAME = NewDatabaseName

    #2  Rename Table
    EXEC sp_rename 
          @objname = 'OldTableName', 
          @newname = 'NewName', 
          @objtype = 'OBJECT'

    #3  Rename Index
    EXEC sp_rename 
          @objname = 'TableName.IndexName',
          @newname = 'NewName',
          @objtype = 'INDEX'

     #4  Rename Column
     EXEC sp_rename 
           @objname = 'TableName.ColumnName',
           @newname = 'NewName',
           @objtype = 'COLUMN'

     #5  Rename Constraint
     EXEC sp_rename 
           @objname = 'ConstraintName',
           @newname = 'NewConstraintName',
           @objtype = 'OBJECT'
     

Remember, #4 only renames the column! It does not account for any referencing objects. For example, if you rename a table that is being called in a procedure, you will need to revise the procedure to reference the new table name. Dropping and re-creating the referencing objects is the recommended way to do this.


Monday, June 1, 2015

How to verify if SQL Server Agent is running?

I read a blog post last week with the title of my subject line. The first thing I thought was wow. You have to check manually? There are no service state alerts in place? But then thinking back, I can remember a few places without formal monitoring solutions in place. In one case, during my 1st week at a new gig, I learned it only after someone else let me know that a service was down. Not good.

There are numerous apps available for monitoring SQL Server, or other Windows services. I've worked with Nagios, Zabbix and several others, but I've also always tried to have something set up on the SQL side of things. That's all this post is. Just a quick script you can use to check the state of the SQL Server Agent, and alert if it is not running.

         SET NOCOUNT ON;

         /*  
           First we load the service name and state into a local variable. 
           Then we check the status, and email and alert if service state <> 'Running'.    */

         -- local table variable to hold our results
         DECLARE @Services TABLE (
        ServerName VARCHAR(50),
       ServiceName VARCHAR(50),
           ServiceState VARCHAR(10)
)

        -- check the SQL Server Agent service
        INSERT @Services (ServiceState)  
        EXEC master..xp_servicecontrol 'QueryState', 'SQLSERVERAGENT'
        UPDATE @Services 
        SET ServerName = @@SERVERNAME,
        ServiceName = 'SQLServerAgent'
  
        -- Check for status <> Running, send email if exists
        IF EXISTS(
        SELECT 1 FROM @Services
        WHERE ServiceState <> 'Running.')
        BEGIN
                EXEC msdb..sp_send_dbmail 
                      @profile_name = 'YourDatabaseMailProfile',
                      @recipients = 'YourDBATeam@domain.com',
                      @subject = 'SQLServerAgent_Service_Alert',
                      @body = 'The SQL Server Agent service is not running. Please review.',
                      @importance = 'High'
        END
        ELSE
                RETURN;

        SET NOCOUNT OFF;

That is just one example targeting the SQL Server Agent. You can very easily change it to check all of SQL Server's services, alerting if any of them are found not to be running. Here's a quick read for you on the other service names, such as the Browser, SSIS or the SQL Server itself.  

Oh yes, you also need to remember, xp_servicecontrol is an undocumented extended stored procedure. Therefore, it is unsupported by MSFT. It could be pulled from the next release, or changed at anytime... without any type of notification. You need to be very wary about using anything like this in production. 


Monday, May 11, 2015

Which SQL Server database is using the most CPU, or consuming the most IO ?

You may have noticed that I haven't posted in quite some time.  I moved during the 1st week of March, and it's been a pretty big transition.  I do apologize for the delay, but I'm back. I have a lot saved in my 'to post' list, and this is the first of many.  :-)

Just a couple quick statements you can use to analyze where your system resources are going. Specifically, your server's CPU utilization and IO file statistics.  


-- Collect CPU usage per database
WITH CPU
AS
(SELECT DatabaseID, DB_Name(DatabaseID)  [Database],SUM(total_worker_time) [CPUTimeMS]
 FROM sys.dm_exec_query_stats dmqs CROSS APPLY (
    SELECT CONVERT(int, value) [DatabaseID] 
    FROM sys.dm_exec_plan_attributes(dmqs.plan_handle)
    WHERE attribute = N'dbid') a
    GROUP BY DatabaseID)
 SELECT 
    [Database], 
    [CPUTimeMS], 
CAST([CPUTimeMS] * 1.0 / SUM([CPUTimeMS]) OVER() * 100.0 AS DECIMAL(5, 2))  [CPU%]
 FROM CPU
 ORDER BY [CPU%] DESC;


-- Collect IO consumption per database
WITH IO
AS
(SELECT DB_NAME(database_id) [Database],CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) [IOMB]
 FROM sys.dm_io_virtual_file_stats(NULL, NULL) dmios
 GROUP BY database_id)
 SELECT 
[Database],
IOMB [TotalIOMB],
    CAST(IOMB/SUM(IOMB) OVER() * 100.0 AS DECIMAL(5,2)) [IO%]
FROM IO
ORDER BY [IO%] DESC;



See here for more detail from MSFT on either of the two above DMVs:

Sunday, March 1, 2015

SQL Server 2012 - the CHOOSE() function

SQL Server 2012 introduced several new logical functions, and in this post I am going to review the CHOOSE function.  Per MSFT, this function 'returns the item at the specified index from a list of values in SQL Server'.  I think it is much like an array would use an index to collect the specified item.

This is a very basic example of the CHOOSE function:

     SELECT CHOOSE(2,'NYSE','CME','ICE','BTEC' ) [Exchange]
     GO

Your results:










The index is an integer expression representing an index into the given list of items. Important to know that the list index always starts at 1.  If you run this one, for example, it will return NULL:

     SELECT CHOOSE(0,'NYSE','CME','ICE','BTEC' ) [Exchange]

Similarly, this will return NULL because there are only four items in my list:

    SELECT CHOOSE(5,'NYSE','CME','ICE','BTEC' ) [Exchange]

Now this example is a little more complex.  I am using the CHOOSE() function with a SELECT from the Person.Person table in AdventureWorks2012. I use CHOOSE() to bring the First, Middle and Last names out, based on the given index. You can see I've also used ISNULL on the MiddleName, to avoid bringing NULL back for any Person entries without a middle name or initial.  

     SELECT 
            CHOOSE(1,FirstName,MiddleName,LastName) + ' ' +
            ISNULL(CHOOSE(2,FirstName,MiddleName,LastName), '') + ' ' +
            CHOOSE(3,FirstName,MiddleName,LastName) [Name]
     FROM
            Person.Person
     GROUP BY
            CHOOSE(1,FirstName,MiddleName,LastName) + ' ' +
            ISNULL(CHOOSE(2,FirstName,MiddleName,LastName), '') + ' ' +
            CHOOSE(3,FirstName,MiddleName,LastName) 
     ORDER BY
           CHOOSE(1,FirstName,MiddleName,LastName) + ' ' +
           ISNULL(CHOOSE(2,FirstName,MiddleName,LastName), '') + ' ' +
           CHOOSE(3,FirstName,MiddleName,LastName) 


And the results...


















Take a look at this from BOL for more information on the CHOOSE() function, and for a couple more use examples:
    https://msdn.microsoft.com/en-us/library/hh213019.aspx

Friday, February 20, 2015

Collect Health and Diagnostic detail from SQL Server

There are a lot of new features in SQL Server 2012.  One of my favorite is sp_server_diagnostics. Using sp_server_diagnostics, you can collect details from your servers pertaining to the System, Resources, Query Processing, IO Subsystem and other Events detail. The procedure has one optional parameter of @repeat_interval.  This is the time iteration that the procedure will use to run repeatedly, to collect the server statistics. The parm has a default of 0, which is used if you do not pass in your own value. In this case, it will collect the data only once, and then exit. I typically pass in a @repeat_interval of 5, which tells the procedure to run, collecting the statistics repeatedly, until it is cancelled. This is the output:



Very generally, these are the descriptions for each of the returned components:

System -- Returns data regarding the system, which includes CPU usage, page faults, non-yielding Tasks, severe processing conditions, access violations, and even spinlocks.
Resource -- Much like it sounds, this collects data regarding the system resources, to include physical and virtual memory, page faults, caching, and other memory objects.
Query Processing -- This component returns data pertinent to the query processing, such as wait types, worker threads and very intense query requests.  
IO Subsystems -- Here we return data for the IO subsystem, to include timeouts and data for long running IO requests.
Events -- Returns data for event exceptions. This would include ring buffer and buffer pool events, memory and security exceptions, and even connection failures.  

If you're responsible for any SQL Servers, I'm sure that you have often had to analyze your server performance. This is a new tool that helps you do just that very quickly. 

Take a look at this piece from Microsoft for more detailed information:
    https://msdn.microsoft.com/en-us/library/ff878233(v=sql.110).aspx

Monday, February 9, 2015

SQL Server -- Different RESTORE Methods

The RESTORE command is what we use to restore database backups, previously created using the BACKUP command (see this post).  Much like BACKUP, you may perform the RESTORE in the gui, but in this post I will show you varied RESTORE methods using tSQL. I am going to be using my DBA database in each of the following examples.  

This would be the most basic form of the RESTORE statement:

     RESTORE DATABASE DBA
     FROM DISK = 'C:\MSSQL\Backup\DBA_20150209.bak'
     GO

In the above example, we are restoring the DBA database from the referenced bak file. Of course, the RESTORE will fail if the bak file is not there.  This is the error you would receive:

    Msg 3201, Level 16, State 2, Line 1
    Cannot open backup device 'C:\MSSQL\Backup\DBA_20150209.bak'. Operating system error 2
    (The system cannot find the file specified.).
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

Ok. Let's assume that you've got the bak file in place, but you're not exactly sure of the database file names or paths for the DBA data/log files.  You can run this statement to provide the logical and physical name of all database files.

     RESTORE FILELISTONLY 
     FROM DISK = 'C:\MSSQL\Backup\DBA_20150209.bak' 


This is a portion of the output from my DBA database:









With these details we could perform the RESTORE as I sampled in the first example, or we will use the MOVE option. This option is used to restore the files to another location on disk. For example, let's say I am restoring the DBA database to another machine, with a completely different directory structure. We would restore the bak file to the D drive on the new box, like this:

RESTORE DATABASE DBA
FROM DISK = 'C:\MSSQL\Backup\DBA_20150209.bak' 
WITH 
            MOVE 'DBA_Primary' TO 'D:\MSSQL\Data\DBA_Primary.mdf', 
            MOVE 'DBA_Data' TO 'D:\MSSQL\Data\DBA_Data.mdf', 
            MOVE 'DBA_Log' TO 'D:\MSSQL\Log\DBA_Log.ldf'
      GO

Or, maybe I want to create a copy of the DBA database, on the same server, yet with a different name. We accomplish that also with the MOVE option, and a new database name, like this:

RESTORE DATABASE DBANEW
FROM DISK = 'C:\MSSQL\Backup\DBA_20150209.bak' 
WITH
            MOVE 'DBA_Primary' TO 'C:\MSSQL\Data\DBANEW_Primary.mdf', 
            MOVE 'DBA_Data' TO 'C:\MSSQL\Data\DBANEW_Data.mdf', 
            MOVE 'DBA_Log' TO 'C:\MSSQL\Log\DBANEW_Log.ldf'
      GO

This next example demonstrates how to RESTORE the database AND differential backup for the DBA database. Note we use NORECOVERY for the first part of the RESTORE of the database backup. This will leave the database unusable, or not fully recovered, until the dif is also restored.

      RESTORE DATABASE DBA 
      FROM DISK = 'C:\MSSQL\Backup\DBA_20150209.bak' 
      WITH NORECOVERY
      GO
      RESTORE DATABASE DBA 
      FROM DISK = 'C:\MSSQL\Backup\DBA_20150209.dif'
      WITH RECOVERY 
      GO

Ok.  What about the DBA LOG backup?  In this example we restore the DBA log from a bak file created on another server, with a different directory structure. We do this using the MOVE option.

      RESTORE LOG DBA
     FROM DISK = 'C:\MSSQL\Backup\DBA_LOG_20150209.bak' 
     WITH NORECOVERY, FILE = 1
          MOVE 'DBA_Primary' TO 'D:\MSSQL\Data\DBA_Primary.mdf', 
          MOVE 'DBA_Data' TO 'D:\MSSQL\Data\DBA_Data.mdf', 
          MOVE 'DBA_Log' TO 'D:\MSSQL\Log\DBA_Log.ldf'
GO

Now we will perform the final RESTORE with RECOVERY, so that the database will again be usable:

     -- Perform final restore
     RESTORE DATABASE DBA
     WITH RECOVERY
     GO

On a slightly different note, how do you know your backup file is even usable?  Maybe it's a copy from somebody else, and you're not aware of how it was created. Here we can use the VERIFYONLY option. This allows us to check the bak file BEFORE running the RESTORE, to ensure that it is valid. This is the statement, and the desired output:



Lastly, one thing that people often overlook is the database ownership.  When you restore a database - in the gui or via tSQL - the database ownership is inherited by whomever is performing the restore. After a restore, right click your database in SSMS, choose Properties;  You can see the Owner is your domain login, rather than 'sa', or any other authorized owner that you may be using.  Easy enough to correct, using this statement:

USE DBA -- change this to whatever database you are targeting
EXEC sp_changedbowner 'sa'