Vision: SQL Homeostasis

You may have situations where the CPU % usage is well below the alert threshold but still queries are running slow because they are waiting for CPU to be available. This script creates an alert to send out email if the number of queries waiting for CPU exceeds the threshold.  Please update the value for the  @ operator  variable to whatever is the email operator you have setup in the SQL Server Agent. And since I am testing I am using the threshold value of 10. You may want to lower that after testing in your environment. Lastly, Since I did not want to get bombarded with emails, I am using the 900 seconds (15 minutes) delay of between alert emails. Please feel free to adjust it to your needs. USE [msdb] GO declare @ operator varchar ( 500 ) -- email operator name declare @ threshold int -- number of queries waiting for the CPU declare @ delay_between_email_alerts int -- this value is in seconds declare @ drop_alert_if_exists bit ...

Most of the times I use the CSV files whenever I need to import or export SQL Server data. And then if I need to do further analysis on the data or simply beautify the results to share with the users or colleagues, I simply open the CSV file in Excel and do the rest of the work manually. But what if I could just skip the CSV and export data directly to Excel format? Wouldn't that save me time and efforts and also help me to automate if I wanted to? No surprise that there is indeed a powershell module for Excel at the Powershell Gallary site. https://www.powershellgallery.com/packages/ImportExcel/5.2.0 You can import the module directly from there or do the manual download. I decided to use the import method. For that I would need to have the PSGallary as one of the registered repositories in the PowerShell If you don't already have registered the Powershell Gallary as one of the repository, there are couple methods depending on the PowerShell version you have. I have the 5.x ve...

If you are new at your job or a new client you would like to gather and review the sql server meta data to understand the environment and get up to speed as quickly as possible. Most of us are already familiar with the DMVs, system functions, procedures etc. to gather the SQL server metadata. And if you want to gather the information for all your SQL servers, you could run a multi-server query against all through the Central Management Server. In fact, in newer versions of SSMS you don't even need the CMS, you just register all your sql instances in the Local Server Groups. So from that perspective this post is not adding much values except maybe that it is another alternative to SSIS or other ETL tools to capture the meta data on regular basis. If nothing else I hope you find this educational regarding how to use powershell to interact with sql servers. <# Export SQL Server properties, settings and configuration values to CSV files #> # name of the sql server instance you wo...

At one of my clients I received an email from one of the IT Project Managers asking a simple question: "Can you please let us know which databases reside on the server below, Server1?" First thought in mind, well from what particular sql instance on that server? At that point I was not even sure if that server has multiple instance, is it a stand alone sever or a node/virtual name of a cluster server, alwayson cluster etc... But I kept that thought to myself. Now, I could launch SSMS, connect to the sql instance, query the sys.databases and get requested information. But I don't know the instance name top of my head. So I would need to RDP into the server or look up the meta data somewhere. Instead of that, I decide to launch the Powershell and issue this command: Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'" -ComputerName "Server1" It has only once sql instnace, great. Then I issued the following c...

This is actually part 2 of a process I am creating to automatically discover SQL Server instances in an Active Directory domain. So there will be a series of handful of posts. You can find the part 1 of this blog series at the following link:  Part 1: Powershell script to find new servers in an AD domain https://sqlpal.blogspot.com/2019/06/powershell-script-to-find-new-servers.html I will be using the CSV file generated by the powershell script mentioned in the above post. In below powershell script all I am doing is to check if the remote servers have sql server instance winodws services setup and their current status. I am not checking yet whether I have access to them or what version of sql servers these instances are running. That will be in the next post in this series! Additionally, in this post I am also inserting the collected information into a sql staging table. But first, if you are just interested in looking up sql server services on a single remote computer, you c...

First, some blah... blah... blah... By default when a primary key constrained is created on a table/view,  SQL Server automatically creates a unique clustered in order to enforce it.  And since a table can only have one clustered index, all the subsequent or any previous indexes created before that are created as a non-clustered index. That works best in most cases and is the recommended best practice. And decision to have clustered index on what columns affects everything about everyday working of an application. And also as a general best practice every table should have a clustered index, but its not required and there are cases where its best not to. Scenario: So now imagine a scenario where a table has the PK but the clustered index is on non PK columns. I am going to assume that there must be well thought-out index strategy for that particular table at the design time. But over time the usage patterns may evolve and/or through endless enhancements, bug fixes etc. now tha...

I should start off by mentioning that this post is applicable to sql server versions 2012 and up. If you have an older version of sql server, the solution discussed here will not work. We are generally aware that a user in a database is orphaned when it does not have a matching SID record in the sys.server_principals table.   This is not an issue if your databases is CONTAINED and uses database authentication. Otherwise, the user will not be able to login into the sql server instance and as a result cannot access the database even though the user has access to the database. Generally, you will get orphaned database users after restoring a database to a different server and one or more users in the database do not have corresponding LOGIN at the instance level or has mismatched SID.  Another possibility is that the login got deleted from sys.server_principals or from the Active Directory or local machine. I am sure there are other possible situations. Microsoft h...