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
DECLARE @path VARCHAR(555)
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.