Using SonarQube, SonarQube Scanner and the sonar-tsql-plugin to run static code analysis

In the previous post Scripting out procs and user defined functions with PowerShell, we scripted out some procs and functions from the Adventureworks database so that we can run some static code analysis. Today we will install SonarQube, SonarQube Scanner and the sonar-tsql-plugin. First thing we need is to grab Java if you don't have it installed. I know I know... I uninstalled it as well.. sigh

Anyway.. after you have Java installed, you can grab SonarCube here: https://www.sonarqube.org/downloads/

Create a folder named sonarqube-7.6 on the C drive, download SonarCube and extract it in C:\sonarqube-7.6\

Next we need Sonar Cube Scanner,  You can download it here: https://docs.sonarqube.org/display/SCAN/Analyzing+with+SonarQube+Scanner

Create a folder name C:\sonar-scanner-cli-3.3.0.1492-windows and extract the Sonar Cube Scanner file there

Finally we need the sonar-tsql-plugin, you can download that here https://github.com/gretard/sonar-tsql-plugin/releases
Grab the file named: sonar-tsqlopen-plugin-0.9.0.jar and download it
Place the jar file in the folder C:\sonarqube-7.6\sonarqube-7.6\extensions\plugins\

Now it's time to create some environmental variables. In an explorer window, paste this into an address bar

Control Panel\System and Security\System
Click on Advanced System Settings, click on Environment Variable, click on new

In the variable name add SONAR_RUNNER_HOME
In the variable value add C:\sonar-scanner-cli-3.3.0.1492-windows

It will look like this

There is one more thing to do, we need to add something to the path
On older versions of windows... add the line below at the end of the path variable, on newer versions, just click on New and paste the line below

;%SONAR_RUNNER_HOME%\bin;

Ok time to run (and fail) SonarQube finally

Go to the folder C:\sonarqube-7.6\sonarqube-7.6\bin\windows-x86-32 and kick off the script StartSonar.bat

If you get an error about 32 or 64 bit, then run the script from the windows-x86-64 folder

If you run the script, if you are lucky, you won't get an error, but if you do is it this one?


jvm 1    | Error: missing `server' JVM at `C:\Program Files (x86)\Java\jre1.8.0_201\bin\server\jvm.dll'.
jvm 1    | Please install or use the JRE or JDK that contains these missing components.

C:\sonarqube-7.6\sonarqube-7.6\bin\windows-x86-32>StartSonar.bat
wrapper  | ERROR: Another instance of the SonarQube application is already running.
Press any key to continue . . .
C:\sonarqube-7.6\sonarqube-7.6\bin\windows-x86-32>StartSonar.bat
wrapper  | --> Wrapper Started as Console
wrapper  | Launching a JVM...
jvm 1    | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
jvm 1    |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
jvm 1    |
jvm 1    | 2019.02.12 13:22:02 INFO  app[][o.s.a.AppFileSystem] Cleaning or creating temp directory C:\sonarqube-7.6\sonarqube-7.6\temp
jvm 1    | 2019.02.12 13:22:02 INFO  app[][o.s.a.es.EsSettings] Elasticsearch listening on /127.0.0.1:9001
jvm 1    | 2019.02.12 13:22:02 INFO  app[][o.s.a.p.ProcessLauncherImpl] Launch process[[key='es', ipcIndex=1, logFilenamePrefix=es]] from [C:\sonarqube-7.6\sonarqube-7.6\elasticsearch]: C:\Program Files (x86)\Java\jre1.8.0_201\bin\java -XX:+UseConcMarkSweepGC -XX:CMSInitiatingOccupancyFraction=75 -XX:+UseCMSInitiatingOccupancyOnly -XX:+AlwaysPreTouch -server -Xss1m -Djava.awt.headless=true -Dfile.encoding=UTF-8 -Djna.nosys=true -Djdk.io.permissionsUseCanonicalPath=true -Dio.netty.noUnsafe=true -Dio.netty.noKeySetOptimization=true -Dio.netty.recycler.maxCapacityPerThread=0 -Dlog4j.shutdownHookEnabled=false -Dlog4j2.disable.jmx=true -Dlog4j.skipJansi=true -Xms512m -Xmx512m -XX:+HeapDumpOnOutOfMemoryError -Delasticsearch -Des.path.home=C:\sonarqube-7.6\sonarqube-7.6\elasticsearch -cp lib/* org.elasticsearch.bootstrap.Elasticsearch -Epath.conf=C:\sonarqube-7.6\sonarqube-7.6\temp\conf\es
jvm 1    | 2019.02.12 13:22:02 INFO  app[][o.s.a.SchedulerImpl] Waiting for Elasticsearch to be up and running
jvm 1    | Error: missing `server' JVM at `C:\Program Files (x86)\Java\jre1.8.0_201\bin\server\jvm.dll'.
jvm 1    | Please install or use the JRE or JDK that contains these missing components.


So to quickly fix this create a server folder in the java bin location from the error message



Now grab the files from the client folder and copy them to the server folder

Ok, we are ready to run the file again, rerun the command and the last 2 lines should be something like this

jvm 1    | 2019.02.12 13:25:53 INFO  app[][o.s.a.SchedulerImpl] Process[ce] is up
jvm 1    | 2019.02.12 13:25:53 INFO  app[][o.s.a.SchedulerImpl] SonarQube is up

Navigate to http://localhost:9000/ login with admin for username and password

Now we need to do one more thing and we are ready, open notepad or you favorite text editor, paste the following

# Required metadata 
sonar.projectKey=StaticCodeAnalysis.project
sonar.projectName=Static Code Analysis project
sonar.projectVersion=1.0
sonar.sources=StoredProcedures,UserDefinedFunctions
sonar.host.url=http://localhost:9000
#sonar.exclusions=**/bin/**/*.*,**/obj/**/*.*,**/*.sqlproj
# Comma-separated paths to directories of source codes to be analyzed. 
# Path is relative to the sonar-project.properties file. 
# Replace "\" by "/" on Windows. 
# Since SonarQube 4.2, this property is optional. 
# If not set, SonarQube starts looking for source code 
# from the directory containing the sonar-project.properties file. 
# Language 
sonar.language=tsql
#Encoding of the source code 
#sonar.sourceEncoding=UTF-8

Save that as sonar-project.properties in the folder where your code is located, in our case it is in C:\temp

Alright.. it's time to run the static code analysis...

Open a command window, cd to the C;\temp folder, and paste following

C:\sonar-scanner-cli-3.3.0.1492-windows\sonar-scanner-3.3.0.1492-windows\bin\sonar-scanner.bat

You should see something like the following

C:\temp>C:\sonar-scanner-cli-3.3.0.1492-windows\sonar-scanner-3.3.0.1492-windows\bin\sonar-scanner.bat
INFO: Scanner configuration file: C:\sonar-scanner-cli-3.3.0.1492-windows\sonar-scanner-3.3.0.1492-windows\bin\..\conf\sonar-scanner.properties
INFO: Project root configuration file: C:\temp\sonar-project.properties
INFO: SonarQube Scanner 3.3.0.1492
INFO: Java 1.8.0_121 Oracle Corporation (64-bit)
INFO: Windows 10 10.0 amd64
INFO: User cache: C:\Users\denis\.sonar\cache
INFO: SonarQube server 7.6.0
INFO: Default locale: "en_US", source code encoding: "windows-1252" (analysis is platform dependent)
INFO: Load global settings
INFO: Load global settings (done) | time=78ms
INFO: Server id: BF41A1F2-AWji9AZ8kkLV5J16bA1i
INFO: User cache: C:\Users\denis\.sonar\cache
INFO: Load/download plugins
INFO: Load plugins index
INFO: Load plugins index (done) | time=31ms
INFO: Load/download plugins (done) | time=47ms
INFO: Process project properties
INFO: Execute project builders
INFO: Execute project builders (done) | time=0ms
INFO: Project key: StaticCodeAnalysis.project
INFO: Base dir: C:\temp
INFO: Working dir: C:\temp\.scannerwork
INFO: Load project settings
INFO: Load project settings (done) | time=16ms
INFO: Load project repositories
INFO: Load project repositories (done) | time=47ms
INFO: Load quality profiles
INFO: Load quality profiles (done) | time=63ms
INFO: Load active rules
INFO: Load active rules (done) | time=1922ms
INFO: Load metrics repository
INFO: Load metrics repository (done) | time=32ms
WARN: SCM provider autodetection failed. Please use "sonar.scm.provider" to define SCM of your project, or disable the SCM Sensor in the project settings.
INFO: Language is forced to tsql
INFO: Indexing files...
INFO: Project configuration:
INFO: 23 files indexed
INFO: Quality profile for tsql: Sonar Way
INFO: ------------- Run sensors on module Static Code Analysis project
INFO: Sensor JaCoCo XML Report Importer [jacoco]
INFO: Sensor JaCoCo XML Report Importer [jacoco] (done) | time=0ms
INFO: Sensor MsIssuesLoaderSensor [tsqlopen]
INFO: Found 0 issues
INFO: Sensor MsIssuesLoaderSensor [tsqlopen] (done) | time=15ms
INFO: Sensor CodeGuardIssuesLoaderSensor [tsqlopen]
INFO: SQL Code guard path is empty, trying to search directories instead
INFO: Found 0 issues
INFO: Sensor CodeGuardIssuesLoaderSensor [tsqlopen] (done) | time=0ms
INFO: Sensor CustomChecksSensor [tsqlopen]
WARN: Property 'sonar.tsql.customrules.paths' is not declared as multi-values/property set but was read using 'getStringArray' method. The SonarQube plugin declaring this property should be updated.
INFO: Total 1 custom rules repositories with total 15 checks
INFO: Sensor CustomChecksSensor [tsqlopen] (done) | time=21548ms
INFO: Sensor CoverageSensor [tsqlopen]
INFO: Sensor CoverageSensor [tsqlopen] (done) | time=16ms
INFO: Sensor JavaXmlSensor [java]
INFO: Sensor JavaXmlSensor [java] (done) | time=0ms
INFO: Sensor HTML [web]
INFO: Sensor HTML [web] (done) | time=15ms
INFO: Sensor Zero Coverage Sensor
INFO: Sensor Zero Coverage Sensor (done) | time=16ms
INFO: ------------- Run sensors on project
INFO: No SCM system was detected. You can use the 'sonar.scm.provider' property to explicitly specify it.
INFO: 21 files had no CPD blocks
INFO: Calculating CPD for 2 files
INFO: CPD calculation finished
INFO: Analysis report generated in 250ms, dir size=127 KB
INFO: Analysis report compressed in 51ms, zip size=40 KB
INFO: Analysis report uploaded in 47ms
INFO: ANALYSIS SUCCESSFUL, you can browse http://localhost:9000/dashboard?id=StaticCodeAnalysis.project
INFO: Note that you will be able to access the updated dashboard once the server has processed the submitted analysis report
INFO: More about the report processing at http://localhost:9000/api/ce/task?id=AWjjFKvAkkLV5J16bDDx
INFO: Analysis total time: 26.228 s
INFO: ------------------------------------------------------------------------
INFO: EXECUTION SUCCESS
INFO: ------------------------------------------------------------------------
INFO: Total time: 28.017s
INFO: Final Memory: 36M/1173M
INFO: ------------------------------------------------------------------------

C:\temp>


When you get the prompt back, it's time to go to the http://localhost:9000/projects URL

You should have 1 project there, the name matches what we had in our properties file

sonar.projectName=Static Code Analysis project

When you click on the project, you will get some information about bugs, code smells and duplication

Clicking on code smells brings back the following.. you can then act on those or not

I added 2 of my own bad procs to see what it would flag

create proc BadProc
as
select * from Person.Address
order by 2,3

I also added this one Unique_Login_IPs, you can grab it here https://social.msdn.microsoft.com/Forums/en-US/55cfe1b0-402a-4468-bf7a-cc0966d4a487/faster-way-to-do-this

As you can see we got some warnings for those procs

SELECT *..   No need to comment on this one
No ASC/DESC in the order by... this defaults to ASC anyway but I guess for clarity it's better to specify ASC
Positional reference is used... I do this all the time with ad-hoc queries but I don't do it with procs

Non-sargeable argument found - column referenced in a function.

That is this line

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 3)) as tmpy1_3

This you would ideally rewrite by doing something like first creating the variable @startdate  and let it have the value @year.03/01 in other words '20190301'

Then the WHERE clause would be something like that

WHERE (method = 'LOGIN') AND logged >= @startdate and logged < dateadd(mm,1,@startdate) 

That is all for this post. Of course you can do some of this stuff with other tools and even with policy management. But if you use SQL Server and many languages, you could do static code analysis from one tool

Scripting out procs and user defined functions with PowerShell

I decided to take SonarCube for a spin to run some static code analysis. In order to do that I needed some code that I can display here without breaking laws.

I a real world, you would just point SonarCube to your code repository, but in order to show you how to run it so that you can follow along, I decided to go this route

I decided to use the AdventureWorks database to script out some procs and functions. If you want to follow what I am doing here, grab the backup from here: https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

On your C drive create a folder named DB, in the DB folder create 2 folders, one name data and the other log

To restore the DB you can run this command (assuming your backup is in the c:\temp directory

USE [master]
RESTORE DATABASE [AdventureWorks2017] 
FROM  DISK = N'C:\temp\AdventureWorks2017.bak' WITH  FILE = 1,  
MOVE N'AdventureWorks2017' 
TO N'C:\DB\DATA\AdventureWorks2017.mdf',  
MOVE N'AdventureWorks2017_log' 
TO N'C:\DB\LOG\AdventureWorks2017_log.ldf',  
NOUNLOAD,  STATS = 5

 Now It's time to script out the procs and user defined functions

But first we need a place to store them

In my case I decided to create 2 folders in the c:\temp directory
StoredProcedures
UserDefinedFunctions

Now it's time to script out the procs and user defined functions, I will be using Powershell from that, you will need to install the sql server module, you can download it here

https://docs.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module?view=sql-server-2017

Follow the instruction if you get any errors.

Now it's time to run the script, you will need to run powershell as an administrator

You will need to change Machinename\sql2019 to your machine and instance name

The script is simple.. it's for a one time use.. if you need to run the script repeatedly, you probably want to make it so you can rerun it for different databases and servers, so no hardcoding  :-)

Import-Module SqlServer -Version 21.0.17279

cd SQLSERVER:\SQL\MAchinename\sql2019\Databases\AdventureWorks2017\StoredProcedures



foreach ($tbl in Get-ChildItem  )

{
$k="C:\Temp\StoredProcedures\" + $($tbl.Schema) + "." + $($tbl.name) + ".SQL"
$tbl.Script() > $k
}

cd ..\UserDefinedFunctions

foreach ($tbl in Get-ChildItem  )

{
$k="C:\Temp\UserDefinedFunctions\" + $($tbl.Schema) + "." + $($tbl.name) + ".SQL"
$tbl.Script() > $k

That's all for the script.. if it ran successfully, you should see a bunch of procs and user defined functions in their directories.  Here is a view of the functions

Next up..installing SonarQube, SonarQube Scanner and the sonar-tsql-plugin

After 20+ years in IT .. I finally discovered this...

Originally I was not going to write this post, but after I found out that several other people didn't know this I figured what the heck, why not, maybe this will help someone else as well

Last week I was on a remote session with 2 clients, each run the Advent program . The team I am part of provides a script to run the advent (APX or Axys) executable. This will then generate the portfolios, composites, price, security master, splits and other files. We then zip it up and sftp it over for ingestion so that we can run analytics and attribution

During these calls I interact with system administrators because usually the need to give permissions so that the script runs correctly

None of these admins knew that what I will show you existed. All the co-workers I asked didn't know this either (This could be because they are developers and not admins)



Back in the day (win 98 or perhaps NT 4), there was a windows powertool that you could install and if you right clicked on a folder you would get an option to open a command window and it would be in the path that you right clicked on

Those power tools don't exist anymore and you could do the same by hacking the registry, it's like a 16 step process

But there is a faster way.....

So what I usually did before 2 months ago is that I would select the path

And then I would open a command prompt, type CD and then paste the path...not too complicated

But here is the faster way.... instead of copying the path...just type in cmd in the address bar and hit enter

Boom shakalaka... a command prompt is opened immediately and you are in the same path

Did you know this also works when you type Powershell in the address bar, Eric Darling left me a comment on twitter informing me that it works with powershell as well

Here is what you see after typing it

So there you have it... hopefully it will save you some minutes of valuable time in a year

Also if you knew about this or did not know..leave a comment and let me know

Print.. the disruptor of batch deletes in SQL

Someone had an issue where a batched delete script was not deleting anything. I looked over some code in our repository and noticed two patterns the way queries are written to handle batch deletes

One is a while loop that runs while @@rowcount is greater than 0

WHILE @@rowcount > 0
 BEGIN
  DELETE TOP (5000)
  FROM SomeTable
 END

The other way is to run a while loop which is always true and then check if @@rowcount is 0, if it is 0 then break out of the loop

 WHILE 1 = 1  
    BEGIN  
        DELETE TOP(5000)  
        FROM SomeTable

        IF @@ROWCOUNT = 0  
     BREAK  
     END

I have always used WHILE @@rowcount > 0 but you have to be careful because @@rowcount could be 0 when your while loop starts

Let's take a look at an example. This is a simplified example without a where clause..but let's say you have to delete several million rows from a table with many more millions of rows and the table is replicated... in that case you want to batch the deletes so that your log file doesn't fill up, replication has a chance to catch up and in general the deletes should run faster

SELECT TOP 20000 row_number() OVER(ORDER BY t1.id) AS SomeId,
  getutcdate() AS SomeDate, newid() AS SomeValue
INTO SomeTable
FROM sys.sysobjects t1
CROSS JOIN sys.sysobjects t2

SELECT COUNT(*) from SomeTable 

SELECT * FROM SomeTable WHERE 1= 0

WHILE @@rowcount > 0
 BEGIN
  DELETE TOP (5000)
  FROM SomeTable
 END

SELECT COUNT(*) from SomeTable 

DROP TABLE SomeTable -- Added here as cleanup in case people run the example

This is of course a silly example because why would you do a count like that against a different table before a delete

But what if you had this instead, you put a nice print statement there so that from the output you see when it started and you would also see the rowcounts?

SELECT TOP 20000 row_number() OVER(ORDER BY t1.id) AS SomeId,
  getutcdate() AS SomeDate, newid() AS SomeValue
INTO SomeTable
FROM sys.sysobjects t1
CROSS JOIN sys.sysobjects t2

SELECT COUNT(*) from SomeTable 

PRINT' Starting my update now....'

WHILE @@rowcount > 0
 BEGIN
  DELETE TOP (5000)
  FROM SomeTable
 END

SELECT COUNT(*) from SomeTable 

DROP TABLE SomeTable -- Added here as cleanup in case people run the example

The count is 20000 before and after the loop, nothing got delete, this is because a print statement will reset @@rowcount  to 0. 

Take a look by running this simple set of queries

SELECT 1 UNION ALL SELECT 2
SELECT @@rowcount as 'Rowcount'
PRINT '1'
SELECT @@rowcount as 'RowcountAfterPrint'

Here is what the output looks like

After the  PRINT line @@rowcount is reset back to 0

So if you want to use a while loop while checking @@rowcount, do this instead by running the delete first once outside the loop

SELECT TOP 20000 row_number() OVER(ORDER BY t1.id) AS SomeId,
  getutcdate() AS SomeDate, newid() AS SomeValue
INTO SomeTable
FROM sys.sysobjects t1
CROSS JOIN sys.sysobjects t2

SELECT COUNT(*) from SomeTable 

PRINT' Starting my update now....'

DELETE TOP (5000)
FROM SomeTable

WHILE @@rowcount > 0
 BEGIN
  DELETE TOP (5000)
  FROM SomeTable
 END

SELECT COUNT(*) from SomeTable 

DROP TABLE SomeTable -- Added here as cleanup in case people run the example

If you run the delete this way if there was something to delete, the while loop would be entered, if the table was empty then there would no need to enter the while loop

Also keep in mind that it is not just PRINT that will reset @@rowcount back to 0.

From Books On Line:

Statements such as USE, SET <option>, DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION, or COMMIT TRANSACTION reset the ROWCOUNT value to 0.

That's all... hopefully this helps someone out in the future if they notice nothing gets deleted

Proactive notifications

It has been a while since I wrote some of my best practices posts. I decided to revisit these posts again to see if anything has changed, I also wanted to see if I could add some additional info.

 Today we are going to look at proactive notifications

In the SQL Server Maintenance post from yesterday I touched upon proactive notifications a little, today I want to dive a little deeper into this subject.

The last thing you want to hear as a DBA is  ny of the following things from the end users or developers

• The transaction log is full
• The database is very slow
• The latest backup we have is 9 days old
• The table that was created has 2 extra columns this morning
• Everything is locked up can't get any results back from a query
• Deadlocks are occurring

What you really want to have at your shop is a tool like Quest Foglight, Solarwinds, Red Gate SQL Monitor or similar. The benefit of these tools is that there is a central location where you can look at all the alerts at a glance. You get a lot of stuff out of the box and all you have to do is tell it what server to start monitoring. I would suggest to start using the trial version to see if it is something that would be beneficial for your organization.

Of course you can roll your own solution as well, this will involve work and unless your time is worthless or you are bored out of your mind after work I wouldn't do it.

Utilize the logs

You need to scan the errorlog periodically to see if there are errors, you can automate this, no need to start opening log files every 5 minutes. create a SQL Agent job that runs every 5 minutes and checks if there are any errors since it last ran. You can use the xp_readerrorlog proc to read the error log from with sql server with T-SQL.

Here is a small example of what you can do if you have this in a SQL Agent job that runs every 5 minutes or so, you can of course email yourself the results, dump the result into a table that is perhaps shown on a dashboard in the office, there are many possibilities.

--This will hold the rows
CREATE TABLE #ErrorLog (LogDate datetime, ProcessInfo VarChar(10), 
ErrorMessage VarChar(Max))

-- Dump the errorlog into the table
INSERT INTO #ErrorLog
EXEC master.dbo.xp_readerrorlog

-- Delete everything older than 5 minutes
-- ideally you will store the max date when it ran last
DELETE #ErrorLog
WHERE LogDate <  DATEADD(mi,-5,GETDATE())

-- Some stuff you want to check for
-- Failed backups...you want to know this
SELECT * FROM #ErrorLog
WHERE ErrorMessage LIKE'BACKUP failed%'

-- Why does it take so looong to grow a file, maybe rethink your settings
SELECT * FROM #ErrorLog
WHERE ErrorMessage LIKE'Autogrow of file%'

-- What is going on any backups or statistic updates running at this time?
SELECT * FROM #ErrorLog
WHERE ErrorMessage LIKE'SQL Server has encountered %occurrence(s) of I/O requests taking longer than%'

-- My mirror might not be up to date
SELECT * FROM #ErrorLog
WHERE ErrorMessage LIKE'The alert for ''unsent log'' has been raised%'


DROP TABLE #ErrorLog 

Those are just small samples, you might want to look for other kind of messages from the errorlog

The transaction log is full

You want to make sure that you know you are running out of space before you run out of space. I covered this in the SQL Server Maintenance post Take a look at the sections Make sure that you have enough space left on the drives and Make sure that you have enough space left for the filegroups In those two section I described what to look for and also supplied code that you can then plug into your own solution

The database is very slow

This complaint you hear every now and then, I have seen this from time to time. There are several things that could be happening, here is a list

Someone decided to take a backup of that 1 TB database in the middle of the day
The update statistics job is still running
Statistics are stale and haven't been updated in a long time
The virus scan is running amok and nobody told it to ignore the database files
Someone decided to query all the data all at once

If you have a tool like Quest Foglight, Confio Ignite, Red Gate SQL Monitor or similar then you can see what query ran at what time, what it did and how long it ran.

You can of course also use sp_who2, BlkBy column and DBCC INPUTBUFFER to see what is going on

If you like to use Dynamic Management Views, then take a look at Glenn Berry's SQL Server 2005 Diagnostic Information Queries  posts, there is a .sql file in each post with all kind of queries to discover all kinds of stuff about your server.

It could also be that your hardware is having issues, make sure the IOs look good and check the eventlog for any clues.

The latest backup we have is 9 days old

The following query will give you for all the databases the last time it was backed up or display NEVER if it wasn't backed up

SELECT s.Name AS DatabaseName,'Database backup was taken on  ' + 
CASE WHEN MAX(b.backup_finish_date) IS NULL THEN 'NEVER!!!' ELSE
CONVERT(VARCHAR(12), (MAX(b.backup_finish_date)), 101) END AS LastBackUpTime
FROM sys.sysdatabases s
LEFT OUTER JOIN msdb.dbo.backupset b ON b.database_name = s.name
GROUP BY s.Name

Here is what the output will look like

DatabaseName LastBackUpTime
--------------  ---------------------------------------
model         Database backup was taken on  NEVER!!!
msdb         Database backup was taken on  12/10/2012
ReportServer Database backup was taken on  NEVER!!!

As you can see that is not that great, all the databases should be backed up on a regular basis. Scroll up to the Utilize the logs section to see how you can check the errorlog for failed backup messages.

Everything is locked up, you can't get any results back from a query

Usually this indicates that there is an open transaction somewhere that has not finished or someone did the BEGIN TRAN part but never did a COMMIT or ROLLBACK.

Some people just restart the server to 'fix' the issue, of course if you do that you will never know what the root cause is and you never know when it will happen again.

We can easily show what happens when you have an open transaction, btw don't do this on the production server.

In 1 query window run this, replace SomeTable with a real table name.

BEGIN TRAN

SELECT TOP 1 * FROM SomeTable WITH(UPDLOCK, HOLDLOCK)

You will get a message that the query completed successfully

In another window run this

SELECT TOP 1 * FROM SomeTable WITH(UPDLOCK, HOLDLOCK)

That query won't return anything unless the first one is commited or rolled back

Now run this query below, the first column should have the text AWAITING COMMAND

SELECT   sys.cmd
        ,sys.last_batch
        ,lok.resource_type
        ,lok.resource_subtype
        ,DB_NAME(lok.resource_database_id)
        ,lok.resource_description
        ,lok.resource_associated_entity_id
        ,lok.resource_lock_partition
        ,lok.request_mode
        ,lok.request_type
        ,lok.request_status
        ,lok.request_owner_type
        ,lok.request_owner_id
        ,lok.lock_owner_address
        ,wat.waiting_task_address
        ,wat.session_id
        ,wat.exec_context_id
        ,wat.wait_duration_ms
        ,wat.wait_type
        ,wat.resource_address
        ,wat.blocking_task_address
        ,wat.blocking_session_id
        ,wat.blocking_exec_context_id
        ,wat.resource_description
FROM    sys.dm_tran_locks lok
JOIN    sys.dm_os_waiting_tasks wat
ON      lok.lock_owner_address = wat.resource_address
JOIN sys.sysprocesses sys ON wat.blocking_session_id = sys.spid

As you can see you have a blocking_session_id and a session_id, this will tell you which session_id is being blocked. You can now verify that the transaction session_id is blocking the other id

Go back to that first command window and execute a rollback

ROLLBACK

The query that had that second select should now be done as well, if you run that query that checks for the waits it should be clean as well.

Of course you could have done the same exercise by running sp_who2, looking at the BlkBy column, finding out what that session is doing by running DBCC INPUTBUFFER(session_id) with that session_id

Deadlocks are occurring

There is already a post written on LessThanDot explaining how you can get emailed when deadlocks occur. Ted Krueger wrote that post and it can be found here: Proactive Deadlock Notifications

Summary

I only touched the surface of what can be done in this post. I want you to find out if there is any monitoring being done in your shop, who gets notified? I have worked in places where the end user was the proactive notification, as long as we fixed it before the business users started to complaint life was good. Manual notifications and homebrew solutions might work for a while but when you add more and more servers and you add more people to the team this becomes laborious and error prone.

Cursors and loops

It has been a while since I wrote some of my best practices posts. I decided to revisit these posts again to see if anything has changed, I also wanted to see if I could add some additional info.

 Today we are going to look at cursors

Why do we hate those poor cursors?

Let's first see why people tend to use cursors. Let's say you come from a procedural language and this is the first time you are using SQL. In the procedural language you know how to traverse a list, you of course will look for something that is similar in SQL........bingo!!! you found it...the almighty cursor....the crusher of almost all SQL Server performance. You start using it, your code works, you are happy, life is good.

Now a team member tells you that the cursor is evil and should never ever be used. You are confused, if a cursor is never to be used then why is it part of the language? Well you might say the same for the GOTO statement, this exists in SQL.  Edsger Dijkstra's letter Go To Statement Considered Harmful was published in the March 1968 Communications of the ACM.

The reason that cursors are evil is that they tend to be slower than a set based solution. Cursors are not needed for 99% of the cases. SQL is a set based language, it works best with sets of data, not row by row processing, when you do something set based it will generally perform hundreds of times faster than using a cursor.

Take a look at this code

CREATE FUNCTION dbo.cursorEnroll ()
    RETURNS INT AS
    BEGIN
        DECLARE @studentsEnrolled INT
        SET @studentsEnrolled = 0
        DECLARE myCursor CURSOR FOR
            SELECT enrollementID
                FROM courseEnrollment
        OPEN myCursor;
 
        FETCH NEXT FROM myCursor INTO @studentsEnrolled
 
        WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @studentsEnrolled = @studentsEnrolled+1
                    FETCH NEXT FROM myCursor INTO @studentsEnrolled
            END;
        CLOSE myCursor
        RETURN @studentsEnrolled
 
    END;

That whole flawed cursor logic can be replaced with one line of T-SQL

SELECT @studentsEnrolled = COUNT(*) FROM courseEnrollment

Which one do you think will perform faster?

What is more evil than a cursor?

If cursors are evil, then what is more evil than a cursor? Nested cursors of course, especially three nested cursors. Here is an example of some horrible code where a cursor is not needed

CREATE PROCEDURE [dbo].[sp_SomeMadeUpName]
AS

DECLARE @SomeDate DATETIME

SET @SomeDate =  CONVERT(CHAR(10),getDate(),112)

EXEC sp_createSomeLinkedServer @SomeDate,@SomeDate,12

DECLARE @sql NVARCHAR(2000), @SomeID VARCHAR(20)



DECLARE SomeCursor CURSOR 
FOR
SELECT DISTINCT SomeID
FROM SomeTable
WHERE getDate() BETWEEN SomeStart and SomeEnd


OPEN SomeCursor

FETCH NEXT FROM SomeCursor INTO @SomeID

WHILE @@FETCH_STATUS = 0

 BEGIN
 
 PRINT @SomeID
 
 SET @sql = ''
 SET @sql = @sql + N'DECLARE @Date DATETIME, @Value FLOAT' + char(13) + char(13)
 SET @sql = @sql + N'DECLARE curData CURSOR FOR' + char(13)
 SET @sql = @sql + N'SELECT * ' + char(13)
 SET @sql = @sql + N'FROM OPENQUERY(LinkedServerName,''SELECT Date,' + RTRIM(@SomeID) + ' FROM SomeTable'')' + char(13) + char(13)
 SET @sql = @sql + N'OPEN curData' + char(13) + char(13)
 SET @sql = @sql + N'FETCH NEXT FROM curData INTO @Date,@Value' + char(13)
 SET @sql = @sql + N'WHILE @@FETCH_STATUS = 0' + char(13)
 SET @sql = @sql + N'BEGIN' + char(13)
 SET @sql = @sql + N'INSERT INTO SomeTAble' + char(13)
 SET @sql = @sql + N'VALUES(''' + @SomeID + ''',@Date,@Value)' + char(13)
 SET @sql = @sql + N'FETCH NEXT FROM curData INTO @Date,@Value' + char(13)
 SET @sql = @sql + N'END' + char(13)
 SET @sql = @sql + N'CLOSE curData' + char(13) + char(13)
 SET @sql = @sql + N'DEALLOCATE curData' 

 PRINT @sql + char(13) + char(13)

 EXEC sp_ExecuteSQL @sql

 FETCH NEXT FROM SomeCursor INTO @SomeID

 END

CLOSE SomeCursor
DEALLOCATE SomeCursor

Why the need of looping over the list of IDs? Join with the linked server and do all this in 3 lines of code

I have seen some really horrible code with nested cursors, one example I saw was when someone needed to sum up some data, he created 3 nested cursor..the first one to loop over years, the second to loop over months and the third to loop over days.  This thing ran forever

All you need is to do a simple group by... for example

;WITH cte AS (SELECT DATEADD(dd,number,'20190101') AS TheDate,number
FROM master..spt_values WHERE type = 'p')

SELECT SUM(number) as Value,

YEAR(TheDAte)as TheYear, 

MONTH(TheDate) AS TheMonth 
FROM cte
GROUP BY YEAR(TheDate), MONTH(TheDate)
ORDER BY TheYear,TheMonth

Output

Replacing one evil with another

If you are using while loops instead of cursors then you really have not replaced the cursor at all, you are still not doing a set based operation, everything is still going row by row. Aaron Bertrand has a good post here, no need for me to repeat the same Bad Habits to Kick : Thinking a WHILE loop isn't a CURSOR

Loops in triggers

Usually you will find a loop in a trigger that will call some sort of stored procedures that needs to perform some kind of task for each row affected by the trigger. In the Triggers, when to use them, when not to use them post I already explained how to handle this scenario

Loops in stored procedures

Here is where you will find the biggest offenders. All you have to do to find the procs with cursors is run the following piece of code

SELECT * FROM sys.procedures 
WHERE OBJECT_DEFINITION((object_id) )LIKE '%DECLARE%%cursor%'

For while loops, just change the '%DECLARE%%cursor%' part to '%while%'

Look at those procs and investigate if you can rewrite them using a SET based operation instead

Loops for maintenance purposes

One of the few times I will use cursors or while loops is if I need to get information about tables or databases where I have to get information from a stored procedure.

Here is an example

CREATE TABLE #tempSpSpaceUsed (TableName VARCHAR(100),
        Rows INT,
        Reserved VARCHAR(100),
        Data VARCHAR(100),
        IndexSize VARCHAR(100),
        Unused VARCHAR(100))
        
        
SELECT name, ROW_NUMBER() OVER(ORDER BY name) AS id
INTO #temp
FROM sys.tables 

DECLARE @TableName VARCHAR(300)
DECLARE @loopid INT = 1, @maxID int = (SELECT MAX(id) FROM #temp)
WHILE @loopid <= @maxID
BEGIN

SELECT @TableName = name FROM #temp WHERE id = @loopid
INSERT #tempSpSpaceUsed
 EXEC('EXEC sp_spaceused ''' + @TableName + '''')
SET @loopid +=1
END

SELECT * FROM #tempSpSpaceUsed

Of course this can be simplified as well, you can just run this instead of the while loop and run the output

SELECT DISTINCT 'INSERT #tempSpSPaceUsed
EXEC sp_spaceused ''' + [name] + ''''
FROM sys.tables

Finally if you need more cursor goodness, take a look at these three posts which are also mentioned in the wiki article

The Truth About Cursors: Part I
The Truth About Cursors: Part II
The Truth About Cursors: Part III

SQL Server Maintenance

It has been a while since I wrote some of my best practices posts. I decided to revisit these posts again to see if anything has changed, I also wanted to see if I could add some additional info.

In this post  we are going to look at SQL Server maintenance

Just like with a car or a house, you need to do maintenance on databases as well. SQL Server has gotten better over the years, there are less knobs you need to turn out of the box but maintenance is still required.


In this post I will be looking at some stuff that you need to be aware of. Some of the things I will mention can be thought of as maintenance as well as regular checks. Think of a DBA as a car mechanic, instead of an oil change, tune up or checking the tire pressure, the DBA will check index fragmentation, run DBCC CHECKDB and make sure you have enough space for the database to grow for the next predetermined period.

The things I will cover in this post are: fragmentation of indexes, free drives space, space in filegroups, running DBCC CHECKDB and finally making sure that you have the latest source code of your objects in a source control system.

Check fragmentation of indexes

A lot of time your index will get fragmented over time if you do a lot of updates or inserts and deletes.

Now instead of rolling your own solution, you should take a look at some of them that are out there and used by many people. Take a look at SQL Server Index and Statistics Maintenance by Ola Hallengren, You can also get the scripts from Github here: https://github.com/olahallengren/sql-server-maintenance-solution

Check that your database is healthy by running DBCC CHECKDB

What does DBCC CHECKDB do? Here is the explanation from Books On Line
Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:

• Runs DBCC CHECKALLOC on the database.
• Runs DBCC CHECKTABLE on every table and view in the database.
• Runs DBCC CHECKCATALOG on the database.
• Validates the contents of every indexed view in the database.
• Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.
• Validates the Service Broker data in the database.

So how frequent should you be running DBCC CHECKDB? Ideally you should be running DBCC CHECKDB as frequent as possible, do you want to find out that there is corruption when it is very difficult to fix since two weeks have passed or do you want to find out the same day so that you can fix the table immediately.

Paul Randal who worked on DBCC CHECKDB has a whole bunch of blog posts about DBCC CHECKDB, the posts can be found here http://www.sqlskills.com/blogs/paul/category/checkdb-from-every-angle.aspx

Make sure that you have enough space left on the drives

Running out of space on a drive is not fun stuff, suddenly you can't insert any more data into your tables because no new pages can be allocated. If you have tools in your shop like cacti then this is probably already monitored. If you don't have any tools then either get a tool or roll your own. Here is how you can get the free space fo the drives with T-SQL

CREATE TABLE #FixedDrives(Drive CHAR(1),MBFree INT)

INSERT #FixedDrives
EXEC xp_fixeddrives

SELECT * FROM #FixedDrives

Here is the output for one of my servers

Drive MBFree
------------------
C 6916  -- System
D 28921 -- Apps
L 52403 -- Log
M 4962  -- System databases
T 86208 -- Temps
U 71075 -- User databases 
V 212075-- User databases 

Here is a simple way of using T-SQL to create a SQL Agent job that runs every 10 minutes and will send an email if you go below the threshold that you specified. This code is very simple and is just to show you that you can do this in T-SQL. You can make it more dynamic/configurable by not hard-coding the drives or thresholds

DECLARE @MBFreeD INT
DECLARE @MBFreeE INT
CREATE TABLE #FixedDrives(Drive CHAR(1),MBFree INT)

INSERT #FixedDrives
EXEC xp_fixeddrives

SELECT @MBFreeD =  MBFree
FROM #FixedDrives
WHERE DRIVE = 'D'

SELECT @MBFreeE =  MBFree
FROM #FixedDrives
WHERE DRIVE = 'E'


DROP TABLE #FixedDrives

IF @MBFreeD < 30000 OR @MBFreeE < 10000
BEGIN
      DECLARE @Recipients VARCHAR(8000)
   SELECT @Recipients ='SomeGroup@SomeEmail.com'
       
  DECLARE @p_body AS NVARCHAR(MAX), @p_subject AS NVARCHAR(MAX), @p_profile_name AS NVARCHAR(MAX)

  SET @p_subject = @@SERVERNAME + N'  Drive Space is running low'
  SET @p_body = ' Drive Space is running low <br><br><br>' + CHAR(13) + CHAR(10) + 'Drive D has ' 
  + CONVERT(VARCHAR(20),@MBFreeD) + ' MB left <br>' + CHAR(13) + CHAR(10) + 'Drive E has ' 
  + CONVERT(VARCHAR(20),@MBFreeE) + ' MB left'

  EXEC msdb.dbo.sp_send_dbmail
     @recipients = @Recipients,
     @body = @p_body,
     @body_format = 'HTML',
     @subject = @p_subject
END

Make sure that you have enough space left for the filegroups

In the Sizing database files I talked about the importance of sizing database files. Just like you can run out of hard drive space, you can also fill up a file used by SQL Server. here is query that will tell you how big the file is, how much space is use and how much free space is left. You can use a query like this to alert you before you run out of space

SELECT
 a.FILEID,
 [FILE_SIZE_MB] = 
  CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)),
 [SPACE_USED_MB] =
  CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,'SpaceUsed')/128.000,2)),
 [FREE_SPACE_MB] =
  CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,'SpaceUsed'))/128.000,2)) ,
 NAME = LEFT(a.NAME,35),
 FILENAME = LEFT(a.FILENAME,60)
FROM
 dbo.sysfiles a

Have the latest scripts of all your objects

You might say that you have all the code for your objects in the database. What if you want to go back to the version of the proc from 3 days ago, is it really easier to restore a 800 GB backup from 3 days ago just to get the stored proc code?

Of course not, make sure that you have DDL scripts of every object in source control, your life will be much easier.

I only touched on a couple of points here, some of the things mentioned here will also show up in the proactive notifications post in a couple of days. There is much more to maintenance than this, keep informed and make sure you understand what needs to be done.

Some more repos for you to use

Take a look at the GitHub repositories mentioned in this post: Five great SQL Server GitHub repos that every SQL Server person should check out  There are some good ones like dbatools and tigertoolbox

Happy Fibonacci day, here is how to generate a Fibonacci sequence in SQL

Image by Jahobr - Own work, CC0, Link


Since today is Fibonacci day I decided to to a short post about how to do generate a Fibonacci sequence in T-SQL. But first let's take a look at what a Fibonacci sequence actually is.

In mathematics, the Fibonacci numbers are the numbers in the following integer sequence, called the Fibonacci sequence, and characterized by the fact that every number after the first two is the sum of the two preceding ones:

 1, 1, 2, 3, 5, 8, 13, 21, 34, ...

Often, especially in modern usage, the sequence is extended by one more initial term:

0, 1, 1, 2, 3, 5, 8, 13, 21, 34, ...

November 23 is celebrated as Fibonacci day because when the date is written in the mm/dd format (11/23), the digits in the date form a Fibonacci sequence: 1,1,2,3.

So here is how you can generate a Fibonacci sequence in SQL, you can do it by using s recursive table expression.  Here is what it looks like if you wanted to generate the Fibonacci sequence to up to a value of 1 million

;WITH Fibonacci (Prev, Next) as
(
     SELECT 1, 1
     UNION ALL
     SELECT Next, Prev + Next
     FROM Fibonacci
     WHERE Next < 1000000
)
SELECT Prev as Fibonacci
     FROM Fibonacci
     WHERE Prev < 1000000

That will generate a Fibonacci sequence that starts with 1, if you need a Fibonacci sequence that start with 0, all you have to do is replace the 1 to 0 in the first select statement

;WITH Fibonacci (Prev, Next) as
(
     SELECT 1, 1
     UNION ALL
     SELECT Next, Prev + Next
     FROM Fibonacci
     WHERE Next < 1000000
)
SELECT Prev as Fibonacci
     FROM Fibonacci
     WHERE Prev < 1000000

Here is what it looks like in SSMS

Happy Fibonacci day!!

I created the same for PostgreSQL, the only difference is that you need to add the keyword RECURSIVE in the CTE, here is that post  Happy Fibonacci day, here is how to generate a Fibonacci sequence in PostgreSQL