blocks in SQL server

There are many things that you can say about locks in SQL server. But to get to the point about locks they cause problems when a user has an exclusive lock on a record and is blocking one or more people. In my experience the whole instance of a blocking problem can work like this. Phone ring user says “The system is really slow, I have been waiting for ever for this data to be displayed”.

You being the SQL server administrator open management studio and from a query window you run “sp_who2”.



image used from http://ravisql2k5.blogspot.com/


Look at the BlkBy column, this will give you the spid (server process ID) that is causing you other users problems. Now it’s decision time, sq_who2 gives you a little more information about the offending spid. You can look at the Login, and possibly get some information from the host-name. Lets say you recognize the Login, you then call the user who is locking everything and see if you can kill his process.
Lets say you don’t recognize the user. You may want to take some time and do more research on what this process is, but you still have the user on the phone. Either way I would say most of the time the solution is killing the process. In your query window type kill and the spid that is blocking progress.
Example if you want to kill spid 55 you type “kill 55” without the quotes. Finally run sp_who2 to make sure everyone is playing fare and not blocking anyone else. Your upset user probably will say “hey I got my data, what did you do?”. At this point you can either be humble hero or all powerful god it’s up to you.

Getting started with Transaction Logs

A log file or transaction log is an essential part of SQL server. The transaction log is the second most important single file in SQL server, second only to the data file it self.

1) What is a transaction log? As the name might imply the transaction log file keeps track of Transactions taken place on the server. Well what is a transaction you might ask. A transaction is any change that takes place on the database server. There are two kinds of transactions that exists in a log file. “committed” transactions, which are transactions that have completed and are kept in case a data recovery is needed. Non-committed transactions are stored in the transaction log but have not completely effected the data yet. Non-committed transactions there for can be “rolled back” meaning they can be reversed. Each database has it’s own transaction log.

2) How to create a transaction log? The transaction log is created at the same time that database is created. If a specific name is not set the log file will be name database name_log.

3) Where to create a transaction log? It is always a good idea to put the log file on a different disk then the data file or the O.S. If you can you should put the transaction log,data file, and O.S. files on their on separate disk systems. Raid systems should also be taken into account when creating the transaction log. Raid 0 is not bad as far as performance is concerned. Raid 0 does not have any redundancy, and there for can be a poor choice for the transaction log. Raid 1 is fast on read operations, but writing operations can be slow and so raid 1 is also a poor choice. Raid 5 is the most common type of raid and is good for performance and data redundancy.

4) How do backups and backup settings effect transaction logs? one of the most important functions of the transaction log is for data recovery. The most fundamental backup setting in SQL server is the recovery model. There are three settings simple, full, and Bulk-logged. In my opinion if you don’t know what model to use then choose full. Simple recovery model only keeps the minimal amount of transactions. All committed transactions are deleted at automatic checkpoints. Leaving little room for disaster recovery. Full as the name implies logs all transactions and gives the best data recovery. Now sometimes you need to import large amounts of data. You can slow your data import by logging everything in the log file. When you are “bulk-importing” you can set the data recovery model to Bulk-logged, and then change it back to full after the import has completed successfully.

XML and SQL server

Let me start this off by saying that I don’t fully understand XML and how it interacts with SQL server 2005 and 2008. I don’t think I am alone here. I have heard nice buzz words like XQuery, XPath, XSLT but I did not know how the pieces fit together. Hopefully I can help make things a little clearer.

1) what is XQuery? XQuery is a query system for XML documents. Basically the SQL for XML documents. Similarly to XSLT, but XSLT is primarily designed to transform XML into HTML. XQuery gives a user the ability to Query XML documents for certain bits of data. The most Current Version of XQuery is 1.0.
XPath is a subset or of XQuery and XSLT.

2) What is XPath? first appeared in 1999. There are two versions that are in use today. version 1.0 was accepted in 1999 and is still the most widely used version. Version 2.0 became official in 2007.

3) What does all this do in SQL server? As you may know from working with SQL server 2005 there is a new data type called XML. Before and still you can save XML documents as text or varchar. There is one distinct advantage to simply saving XML as text, and that is you can Query it. The tool that you use to navigate the XML data type is XQuery. Hope this clears up some confusion that you may have about these concepts.

ODBC, OLEDB and SQL server .net

ODBC, and OLEDB are application programming interfaces designed by Microsoft. They have become universal APIs for accesses data in most major databases.

ODBC was created 1992. The goal of ODBC (Open Database Connectivity) was to make a layer of abstraction that would free an application from programming language, database system, and operating systems constraints. All 4 of the major database systems in the world support ODBC.

Its successor is OLEDB ( Object Linking and Embedding Database) is said to be faster and support more features. Again there is drivers for all 4 major DBMS. The benefits of abstraction layers are to add flexibility, but these layers can also degrade performance.

If you are developing an application using VS.net and the intended database is SQL server you should be using SQL server.net (system.data.sqlclient namespace). This data provider is SQL server specific, but is the fastest way to access data from sql server.