SQL Server Kill Process

The kill command is used against a SPID. (Server Process ID). This command is typically used because something is misbehaving. In order to use the Kill command, simply type the word “kill” followed by a space and then the number of the Server Process ID to kill. [cc lang=”sql”] — Kill Server process ID 98 kill 98 [/cc] The kill command has three parameters. Two of which I have no idea what they mean: KILL { session ID | UOW } [ WITH STATUSONLY ] Continue reading ...

Best SQL Server Pagination Method

SQL Server 2012 actually employs a different method for pagination that is built in using the ORDER BY Clause. However for other versions, the most efficient pagination method is actually simple. Aren’t most things like this? It involves using (the mighty powerful) ROWCOUNT statement — twice. Anyways, the code: [cc lang=”sql”] CREATE PROCEDURE [dbo].[spUsersPaged] ( @RowBegin int, @MaxRows int ) AS BEGIN DECLARE @StartID int SET ROWCOUNT @RowBegin SELECT @StartID = UserID FROM dbo.Users u ORDER BY UserID — Set maximum number of rows to return SET ROWCOUNT @MaxRows SELECT * FROM dbo.Users u WHERE UserID >= @StartID ORDER BY u.UserID SET ROWCOUNT 0 END [/cc] That’s it for the first pagination sample. You may try other methods, however I challenge all Ye to overcome thy double ROWCOUNT method. Comment below please. To give a variety, I’m sharing another pagination method. This one seems more elegant. It uses a Common Table Expression) CTE, and the RowNumber function. [cc lang=”sql”] SET @rowsPerPage = 10 SET @pageNum = 3 WITH SQLPaging AS ( SELECT TOP(@rowsPerPage * @pageNum) ResultNum = ROW_NUMBER() OVER (ORDER BY id) ,id FROM dbo.Table1 ) SELECT * FROM SQLPaging WHERE ResultNum > ((@pageNum – 1) * @rowsPerPage) [/cc] Yet is it elegant, with speed?? ..uh not so much.

Using sp_who2

This article shows the usage of sp_who2. To diagnose system slowdowns, see (Troubleshooting SQL Slowness). One of the first lines of defense in determining the causes of database slowdowns is to use sp_who2. sp_who2 shows all the sessions that are currently established in the database. These are denoted as SPID‘s, or Server process Id’s. Running sp_who2 is easy, all that is required is to type sp_who2 and execute it, however it’s a good idea to call it with EXEC. [cc lang=”sql”] EXEC sp_who2 [/cc] The first 50 results are system SPIDs. Generally these do not effect slowdowns of the system. These system events include the Checkpoint writer, Log writers and task schedulers. User processes are SPID numbers 50 and over. In diagnosing slowdowns it is these SPIDs that are the potential resource hogs. sp_who2 also takes a optional parameter of a SPID. If a spid is passed, then the results of sp_who2 only show the row or rows of the executing SPID. [cc lang=”sql”] EXEC sp_who2 243 [/cc] There are four main things to look for when when diagnosing slowdowns. Blocking High CPU usage High IO usage Multiple entries for the same SPID representing parallelism. When a number is shown in the column named BlkBy, this represents the SPID that is currently stopping the SPID in the row shown. Sometimes many rows will show SPID numbers in the BlkBy column. This is because there is a chain of blockers. The way this occurs usually starts with one “lead” blocker blocking […] Continue reading ...

Cannot Log in Remotely to SQL Server Express

SQL Server Express 2008 does not allow remote connections by default.  This is done by design as SQL Express is often used either as a development tool, or as a local installation for resident applications.  Another thing to note, is that SQL Express uses dynamic ports by default so in order to connect, The SQL Server Browser service needs to be started on the same machine that Express is installed on. Below are the steps to perform: Enable TCP Connections for SQL Server Express Start -> Programs -> Microsoft SQL Server 200x -> Configuration Tool -> SQL Server Configuration Manager Expand SQL Server Network Configuration Click Protocols for SQLExpress In the right hand pane, right click TCP/IP and choose ‘Enable’ Make sure SQL Server Browser Service is started. Without this service enabled, you will not be able to connect (unless you specified a static TCP port). As long as you do not have any firewall issues, you should be able to connect