Sunday, November 10, 2019

Where is the SQL Server install path?

Why do you need to know where SQL is installed?  Funny little thing... someone built out a new server to do a migration.  New hardware, new software, all the bells and whistles needed for the new box to zoom!  Only it didn't.  It was remarkably slower than the old box.  Queries completing in 1 second on old server were running 30+ seconds on the new server.  Not just queries, actually -- everything was slower.

I looked and looked, but couldn't find it... so I backed up and just did a side by side comparison of the two servers using xp_msver.  Both the OS and SQL Server level, checked everything I thought may be relevant -- platform & sql server version, cpus, max memory, etc.  And then I found it!  In part.  😏

The Platform on the old box is NT x64, the platform on the new box is NT INTEL X86.  We know that X86 = 32bit, so that must be it... right?  But I look at the system properties (right click 'This PC' in File Explorer, choose Properties), and it tells me this:

     System type:  64-bit Operating System, x64-based processor

So why does xp_msver say X86 and the Windows gui says X64 ?  

Simple.  The 32bit version of SQL server 2014 Enterprise version was installed, rather than the intended 64bit.

You can go browsing File Explorer to check Program Files(x86), or you can run the quick query below to show you the install path.  x86 means 32bit, and that is why the server was dragging.  Mistakes happen... this was definitely an interesting one to find!  If you run into the same unexplained server slowness, check that platform and be sure you're running what you think you are.

-- find install path
EXEC master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @path OUTPUT

SELECT @path [install path]

This is my own:

Back with more on xp_msver later.

Wednesday, May 8, 2019

SQL Server Database - Is Auto_Close Enabled?

Should Auto_Close be ON for any of your databases?  In short, no.  Having the Auto_Close option ON = increased overhead.  Or, per MSFT, "...this option can cause performance degradation on frequently accessed databases because of the increased overhead of opening and closing the database after each connection."  

In my book that means turn it off.  Here's a quick query to show you which databases have Auto_Close ON:

       -- is auto_close enabled
              name [Database],
              CASE WHEN is_auto_close_on  = 0 THEN 'No'
                      WHEN is_auto_close_on = 1 THEN 'Yes' END AutoCloseEnabled
       FROM sys.databases

And this is what you use to set it OFF:

       -- set auto_close off

       ALTER DATABASE ReportServer

Take a look at both of these for more details:
     Set the AUTO_CLOSE Database Option to OFF

Tuesday, April 30, 2019

Quick count on all SQL Server Object Types

How many times have you copied objects from database A to B, and needed to compare object counts when complete?  Or, maybe you just want to see what's in one database that is not in another?  Here's a quick piece to give you object type counts in your database, and another to show you what's in one database that's not in the other.

-- object type counts
       'Count' = COUNT(*),
       'Description' = CASE [Type]
       WHEN 'AF' THEN 'Aggregate function (CLR)'
       WHEN 'C' THEN 'CHECK Constraint'
       WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)'
       WHEN 'EC' THEN 'Edge Constraint'
       WHEN 'F' THEN 'FOREIGN KEY Constraint'
       WHEN 'FN' THEN 'Scalar functions'
       WHEN 'IF' THEN 'SQL Inline Table-valued Function'
       WHEN 'IT' THEN 'Internal table'
       WHEN 'P' THEN 'SQL Stored Procedure'
       WHEN 'PK' THEN 'Primary Key'
       WHEN 'R' THEN 'Rule (old-style, stand-alone)'
       WHEN 'RF' THEN 'Replication-filter procedure'
       WHEN 'S' THEN 'System base table'
       WHEN 'SN' THEN 'Synonym'
       WHEN 'SO' THEN 'Sequence  Object'
       WHEN 'SQ' then 'Service Queue'
       WHEN 'TA' THEN 'Assembly (CLR) DML trigger'
       WHEN 'TF' THEN 'SQL table-valued-function'
       WHEN 'TR' THEN 'SQL DML trigger'
       WHEN 'TT' THEN 'Table type'
       WHEN 'UQ' THEN 'UNIQUE Constraint'
       WHEN 'U' THEN 'User Table'
       WHEN 'V' THEN 'View'
       WHEN 'X' THEN 'Extended stored procedure'
       ELSE type END
       sys.objects s


Here's the output from my AdventureWorks2012 database:           

And this piece will tell you what tables are in the database you are connected to, that is not in the 'DatabaeName' on the other 'ServerName' in the query:  

SELECT FROM sys.objects a
WHERE a.type = 'U'
       SELECT 1 FROM ServerName.DatabaseName.sys.objects b
       WHERE b.type = 'U'
       AND =

Of course, you can change it to any object type, or even all objects... and you'll need to have a linked server for 'ServerName' in order for it to work.

Hope it helps!

Sunday, April 28, 2019

How to find all startup stored procedures?

I upgraded an instance from v2008 to v2014 over the weekend.  Fortunately, everything went well, but this was one of the prerequisites:

  • Disable all startup stored procedures, as the upgrade process will stop and start services on the SQL Server instance being upgraded. Stored procedures processed at startup time might block the upgrade process.

So I ask myself -- how do I find all startup procs?  Very simple.  Run this query to identify what procedures are configured to run at startup:

  -- find procs that run at startup
  SELECT name,create_date,modify_date
  FROM sys.procedures

And run this to tell the system to run it at startup, or not:

  -- edit to run at startup, or not
  EXEC sp_procoption
   @procname = 'usp_MssqlServiceStart',@optionname= 'startup',@optionvalue = 'off'

As I said, very simple.  Take a look at this for more details on sys.procedures:

And even this for more details on the SQL Server upgrade:

Thursday, April 25, 2019

How to find out when SQL Server was installed?

Good question.  How do you find out when SQL Server was installed?  I believe there may be a few different methods but the one that I use is pretty simple.  I just query the system for something that is created at the time of the install --- the NT AUTHORITY\SYSTEM login.  Pretty easy... as you can see here.

              SERVERPROPERTY('productversion') ProductVersion,
              SERVERPROPERTY ('productlevel') ProductLevel,
              SERVERPROPERTY ('edition') Edition,
              SERVERPROPERTY ('MachineName') MachineName,
              create_date 'SQL Server Installation Date'
              name='NT AUTHORITY\SYSTEM'

Here's the output from one of my instances:

And that's that!

Monday, April 22, 2019

How to disable or re-enable table constraints

Say you are copying data to one table from another, and you need to work around a  particular FOREIGN KEY constraint.  Or, maybe you you need to work around ALL FK constraints on that table.  We'll often need to work around FK constraints in situations like these.  This post is just a quick how-to, for a single table constraint, or all constraints for a table.

For all constraints on a table, replace 'TableName' with your table name:

       -- Disable all table constraints, whole database

       ------ do your table insert here

       -- Enable all table constraints, whole database

For a single constraint on a table, replace 'TableName' and 'ConstraintName' with your specific object names:

       -- Disable single constraint, single table
       ALTER TABLE TableName NOCHECK CONSTRAINT ConstraintName

       ----- do your table insert here

       -- Enable single constraint, single table
       ALTER TABLE TableName CHECK CONSTRAINT ConstraintName

There you have it.