SQL query tuning front end Website slowness Overview of the issue. There is a health care website which used by doctors, in a day which runs slow sometimes and runs fine most of the time. The slowness is about 1 to 3 seconds and it sometimes timeout as well, when it reaches timeout setting. I have configured server side trace, whoisactive and Perfmon counters as well to correlate with anything when needed. As a basic check, the website is fetching data from two databases one is 300 MB and other is 500 MB and no maintenance job been run in place. Have configured maintenance job, still same issue after a…
-
-
How to use Blitz Brent Ozar First Responder Kit
How to use Blitz Brent Ozar First Responder Kit This post is in my draft very long time, I have not get good time to write in better way. I will be updating this periodically. You can download it from website https://www.brentozar.com/first-aid/, unzip it and run all SP starts with SP*. I have learnt most from GitHub https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit I have used DBAData database to create all scripts and load data. ====================== SP_Blitz — Easy way to get server level setting and health check --sp_Blitz use DBAData go /*Check only the priority id up to 50*/ exec sp_Blitz @IgnorePrioritiesAbove = 50-- Default Important parameters –@Help = 1 — explains the…
-
SQL server daily health check DBA script
The following script will help to do a quick health check of SQL estate that you manage. To check all SQL server health check, you need to create Registered or Centralized server. I usually automated everything using my AutoMon script package that will trigger an email from CMS server. But, some small clients and where I do not have long term scope, I use Registered Server and will run various script than the health check like – DB list, Login list & agent job list etc. -- 1) Disk Free Space Check XP_FIXEDDRIVES -- 2) DB Online Status (Including SQL 2000) SELECT NAME,DATABASEPROPERTYEX (NAME,'STATUS') AS STATUS, GETDATE() as [Today_Date]…
-
Not in where clause SQL query running slow taking more time
Let me give an overview of one of multi join Ad hoc query with couple of where condition, which I got received to improve the run time speed. I got a statement from a developer that one of code is running long time, more than 15+ hours, which usually runs in less than a minute. I have asked them how a less than minute code will run more than 15 hours, they said no changes made, I am sure they are lying and it is a Ad-hoc, I cannot trace the DDL changes. I started doing basic check and it is an eight table join with group by and order…
-
The operation cannot be performed on a database with database snapshots or active DBCC replicas
Background of the issue. There is a script stored procedure for both backup and restore is running on one of the server. I have got this from one of our customer. I have checked the restore and looks like there was a database snapshot been created that was a part of backup stored procedure. Msg 5094, Level 16, State 2, Line 1 The operation cannot be performed on a database with database snapshots or active DBCC replicas. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. Solution: Drop the database snapshot and restore the database. Drop database <Snapshot DB Name> T-SQL to find the database snapshot…