Sql Server Deadlock

If your application has lot of traffic and it involves databases then you might run into Deadlock scenarios. A deadlock happens when 2 threads are holding a lock for some resource and waiting on each other to release the lock so that they can continue.

In Sql Server deadlock is handled by the SQL Server Lock monitor thread and when a deadlock occurs it decides which process to terminate based on the priority of the process.

One of the most important thing you need to do, while setting up the SQL Server is turning on the TraceFlag 1222 in the startup parameters. What this does is whenever a Deadlock occurs it will add the deadlock information in the errorlog file, which you can use to analyse why deadlock occurred.

The error log is pretty detailed and you can figure out a lot of information from that. Its like a postmortem analysis, when trying to figure of a issue in production. There is a crime scene, culprit, symptoms and victims.

As you can see from the ErrorLog above, it gives you which process is holding which resource and what type of lock it has. It gives you which Sql Statements caused the deadlocks and the sqlHandle, process Id. It is pretty self descriptive but very informative.

The picture below shows diagrammatically, same info whats there in the error logs. It has helped me a lot to create a picture whenever a deadlocks occurs so that I can figure what exactly is happening.


These are some ways you can try to handle deadlocks situations.

1) Keep the transactions small.
2) Do nibble deletes/updates/inserts i.e fews records at a time and repeating the operation.
3)Using a Retry logic and see if it succeeds the second time/third time etc.
4) Adding WAIT FOR for sometime. so that the transaction which holds the local can complete.

I love SQL Server and love thinking in terms of Sets. Still trying to learn !

Hope this helps someone

Thanks,
Yash

Order of Execution (SQL)

Sequence in which a SQL statement is executed inside a SQL server.

How the transaction logs are created in SQL Server.

SQL Server Full Recovery Model 

1) You have to take Daily back up suppose once a day. when the load on the system is low.

2) Take Transaction Log back at suppose every 5 minutes interval.

Taking Transaction Log backup  will reduce the size of the Log File, because whichever VirtualLogFile are not used (not active) are written to the Transaction backup file and now the same VirtualLogFile can be reused by SQL Sever. Apart from this log Truncation also happens. 

With Full Recovery model you can almost recreate the database, by first restoring the Daily Backup and then applying the transaction log backup by the DateTime they were created.

Same principle is used for log shipping. 

The transaction logs are continuously copied to the logshipped instance  by a SQL Job and then restored on the logshipped intance. The idea is the same.

You can find some informative free videos from this site about SQL server.

http://www.sqlservervideos.com/

Peace!

Yash



Sql Queries

Lately I have working a lot on SQL Server and figuring out issues with the Production application, like deadlocks, slow running queries etc.

I wanted to create a repository  of the queries I use to figure out the issues. It might be help me if I forget the queries down the line :), and maybe help someone along the way.

SQL Blocking Queries and DeadLock Detection queries::

Select * from sys.sysprocesses where blocked > 0
select * from sys.fn_get_sql({Sql Handle})
select * from sys.traces    (Get the trace file path and check if there are any deadlocks)
kill 176  (Kill long running queries where 176 is the spid)
sp_updatestats 


LogShipping Queries::

use msdb
select * from log_shipping_monitor_primary
select * from log_shipping_primary_secondaries
select * from log_shipping_monitor_history_detail
select * from log_shipping_monitor_error_detail


Sql Profiler ::

When you are debugging, sometimes you need to see what exact database query is getting executed. I extensively use Sql profiler to view the queries and the events that I choose in the filter are

1) Sql Batch Completed
2)  RPC completed

With these 2 selected you can find the exact query and dont have to go through a bunch of other sql trace logs.

-Yash

SQL

I have been working on a project where I am getting an opportunity to work in SQLServer Database, writing complex queries, Sql Server Agent Jobs, Scheduler, LogShipping , Database Mirroring. I always wanted to work in such a project where I can improve my sql skills. I love sql as a language. I always loved how much power SQL has.

I wanted to point out 4 techniques in SQL if mastered correctly can really separate you from rest of the pack.

1) SubQuery

This is a simple subquery to find the nth largest number from a column. Inner query is executed first then the outer query uses those results.

SubQuery

2) Correlated Query in the WHERE Clause and the SELECT Clause.

Correlated Query can be tricky because it need a little different way of thinking. This example is from the Northwind database. Its about finding the first order each of the customers placed.



Correlated Subquery in Where Clause
 

Correlated Query can be done in the Select Clause as well. This query is about finding the CustomerName and the FirstOrder they placed.

Correlated Subquery in Select Clause

3) Derived Table Approach
4) TemporaryTable Approach

Always breakdown the question in small parts!!. Figure out which tables are involved in the query, i.e from which table you can get the information you want. Then if you are doing aggregates then make sure, that the query you write is handling the Duplicates. Sometimes when you have hierarchical data, you can overlook the duplication and might be tempted to use the distinct keyword. If the normal joins (inner/outer) doesn't give you the information you need, fall back to these techniques and try to figure out the query.

Remembering how SQL is executed is important when try to fix some parsing issue with complex queries.

Thanks,
Yash

Improving performance and Security of your webpages/sites

I almost stopped writing blog. Things were happening with my life which I was not happy about and I kind of lost some of my interest in writing.

Coming back to out topic for today, I decided to write about common things to that web developer has to watch out for while working.

The main thing here is  ::

 Keep the markup small and Http calls to the minimum.

1) Try to minimize the Http calls from the browser to your server.
2) Combining Scripts files (js)  into one using Minification. Minifier generally  change the variable names to small names which reduces the size of the script files as well.
3) Using Cache-Control header. You need to separate your code/script/image files into separate directories. This way you can cache the contents for different time periods.
4) E-tags 
5)Css Sprites -- Combining your different images into one image and then rendering the images using CSS classes which will set the position based on the combined image. This will reduce the Http Calls for each of those images.
6) Rendering the Scripts at the end of the page. This will help page load time.
7) Using UnObstrusive Javascript. What this means is using data- attribute to render your attributes and then use javascript after the pageload to do some processing based on that.
8) Fav.icon -- Moving the Fav icon by to the images folder so that it can be cached and rendering the new path from your page. Browsers by default make call to this image with every request.
9) Reducing the Unwanted HttpHeaders
10) Use Content Delivery Network (CDN) 
11) Use HttpCompression functionality provided by IIS

Keeping in mind following Security pitfalls at all times

1) Xss Attack -- You should not take any user input without encoding it.
2) CSRF Attack --  Making sure that you pass a token (Anti Forgery Token ) to your server, while making HttpRequest. So that you know that the request is made by the user and not by some other script rendered from some different server.
3)Sql Injection


Thanks,
Yash

Debugging Tips and Tricks

1) Its been a while, since I last wrote. Today, I want to write about Debugging. I enjoy debugging and love to spend time in code, figuring out how stuff works. This is not a new trick, lot of people might be already using it, but I recently used this, so I thought of writing about it.

When you have a web application which is communicating with other services using WCF or  REST or using WebRequest. Sometimes its hard to debug some scenarios where, you are not getting much help from the EventLogging.

In such scenarios, you might want to see what is happening on the server when these calls are made or what was the request/response you received from other services.  You can use FIDDLER in such scenarios, and route all the calls through Fiddler, since Fiddler acts as a proxy. Once you set the proxy to fiddler, you can open fiddler on the server and see all the calls that are being made from your webapplication. Fiddler by default listens at port 8888 or you can change that in fiddler options.

For this to work, you need to add following entry in Web.Config file

2) This is not a debugging tip but I was recently debugging some issue at work and wanted to open one of the dll's in reflector, on the Test sever which was installed in GAC.

When you open the assembly folder, it doesnt give you an option to download or look at the dll. One way to get the DLL from GAC  is to open the folder in command prompt, then you can see that inside the assembly folder, there are couple of folders

\assembly
   \GAC_32
   \GAC_MSIL
                                 etc

Inside these folder you can find the dll you are looking for by appropriate name/version.

-Yash

First blog entry from my IPhone!!

This is my first blogging entry from my iPhone. I am super excited about my IPhone. I love it , its a programmers playfield!

Thanks,
Yash