Working with large tables in SQL Server

by Andrei Hetel 1. June 2013 11:56

Example of a bad written query, take a look at the snippet below. At the time it was written the table size was very small couple of hundred records maybe. On the RequestedPage field there is clustered index.

DECLARE @description NVARCHAR(MAX)
DECLARE @ArticleLinkId INT
-- Obtain the value for @description variable using a simple process - not important here
SELECT @ArticleLinkId = LinkId FROM PageCollection with (FORCESEEK)  WHERE RequestedPage = @description

Query plan look like in the following picture:

Index Scan

That 'simple' operation took over 30 seconds to complete. Please note by looking at 'Estimated Number of Rows' that the table has more 29 million records. It took some time until I realized that the data type of @description variable was NVARCHAR(MAX) and only after I tried to use FORCESEEK hint (which of course didn't work).

By simply changing the data type to VARCHAR(255) which is also the data type of RequestedPage column everything start to fly, see picture below.

Index Seek

As a conclusion, for a small table it doesn't make any significant difference, but for large table it does. Happy coding!

Usefull SQL Server script

by Andrei Hetel 23. March 2013 09:21

It happen to me a lot of time to forget some relations that I have in my databases. Instead of following the foreign keys and do a lot of clicking, I prefer to run this stored procedure.

 

CREATE PROCEDURE FindAllColumns
(@columnName VARCHAR(255))
AS
BEGIN

	SET NOCOUNT ON;
	
	SELECT 
		T.name AS TableName,
		C.name,
		CT.name AS DataType,
		C.max_length,
		C.precision,
		C.scale
	FROM	 
		sys.columns C,  sys.tables T, sys.types  CT
	WHERE 
		C.system_type_id = CT.system_type_id 
		AND C.object_id = T.object_id 
		AND c.name = @columnName

END 

To find all tables that contain the column ArticleId, run it like this:

 

EXEC FindAllColumns 'ArticleId'

SQL Server error handling

by Andrei Hetel 22. October 2012 07:50

Starting with SQL Server 2005 the old error handling was improved with TRY CATCH block. That was a significant step forward. I wrote this post mostly to remember how to output all the details about the error message, keep forgetting.

BEGIN TRY
        -- Force an error here for testing purposes
        SELECT 1/0
END TRY

BEGIN CATCH
    PRINT 'sql server error number: ' + CAST(ERROR_NUMBER() AS VARCHAR)
    PRINT 'sql server error message: ' + ERROR_MESSAGE()
    PRINT 'sql server error severity:' + CAST(ERROR_SEVERITY()  AS VARCHAR) 
    PRINT 'sql server error state:' +  CAST(ERROR_STATE() AS VARCHAR)
    PRINT 'sql server error procedure:' + ERROR_PROCEDURE()
    PRINT 'sql server error line:' + CAST(ERROR_LINE() AS VARCHAR)
END CATCH  

SQL Server MemToLeave

by Andrei Hetel 9. February 2012 13:11

 

The last SQL Server problem that I was facing was too interesting - so I decide to put it here quickly.

I'm using SQLCLR (SQL Common Language Runtime) for a very long time and never had any major issue with it until yesterday. Error message, as follows:

 

.NET Framework execution was aborted by escalation policy because of out of memory.

 

SQL Server error log gives no more information. So, I start searching for a solution. I thought that I'll be lucky because in the first 10 minutes I found and download a patch from Microsoft that was solving exactly the issue that I described above. But... no, it was a patch for SQL Server 2005 SP1 and SP2 was already installed on the server (forgot to mention that the serves is not a beast but still a decent machine). As a conclusion didn't let me install it, saying that issue was addressed in SP2 (obviously not - or more exactly not in my case).

 

 

Another unhappy programmer was lucky to solve a problem using this statement:

 

DBCC FREESYSTEMCACHE ('ALL')

 

 

As you can imagine, it didn't work. I read a lot of stuff about configuring SQL server memory, MemToLeave usage ect and I won't enter in detail explaining how it works. Last resort was to modify a SQL Server startup parameter (famous -g).

 

For this you need to open SQL Server Configuration Manager and select your instance, see picture.

 

SQL Server Configuration Manager

 

Right click, Properties, Advanced. Modify "Startup Parameters" by adding -g500 (for example - default value is 256).

 

SQL Server Service Properties

 

Happy coding!

 

Silverlight 3 combobox selectedvalue

by Andrei Hetel 25. July 2011 05:22

I was very unpleasant surprised to find out that there is no selectedvalue property for a combobox in Silverlight 3. Most of the time you want to perform an action based on a combo box selection change. So, I come up with a simple solution which will be detailed below.

 

XAML code:

<ComboBox x:Name="MyColors">

 

We need two simple classes to bind the combobox:

 

Public Class CComboItem

     Private _ID As Int64
     Private _Description As String

     Public Sub New(ByVal pid As Int64, ByVal pDesc As String)
         _ID = pid
         _Description = pDesc
     End Sub

     Public Property ID() As Int64
         Get
             Return _ID
         End Get
         Set(ByVal value As Int64)
             _ID = value
         End Set
     End Property

     Public Property Description() As String
         Get
             Return _Description
         End Get
         Set(ByVal value As String)
             _Description = value
         End Set
     End Property

End Class

 

Second class, more exactly a collection:

 

Imports System.Collections.ObjectModel

Public Class CComboList
     Inherits ObservableCollection(Of CComboItem)

     Public Sub addItem(ByVal itm As CComboItem)
         Add(itm)
     End Sub
End Class

 

Load the combo as follows:

 

Dim colorList As New CComboList
colorList.Add(New CComboItem(1, "red"))
colorList.Add(New CComboItem(2, "yellow"))
colorList.Add(New CComboItem(3, "blue"))
MyColors.ItemsSource = colorList
MyColors.DisplayMemberPath = "Description"
MyColors.SelectedIndex = 0

 

Finally, because the post is already too long, get selected value from the combo box:

 

Private Sub MyColors_SelectionChanged(ByVal sender As Object, ByVal e As System.Windows.Controls.SelectionChangedEventArgs) Handles MyColors.SelectionChanged
     Dim id As Int64
     If MyColors.SelectedItem Is Nothing Then Exit Sub
     ' here is the selected value...
     id = DirectCast(MyColors.SelectedItem, CComboItem).ID
End Sub

 

Pretty simple, but at least is doing the trick.

 

SQL Encryption

by Andrei Hetel 26. April 2010 05:50

In case you don't know yet, there is an option in SQL Server to kind of 'protect' your database objects (stored procedures, triggers, views etc). That is achieved by using 'WITH ENCRYPTION' keyword.

 

CREATE PROCEDURE Test
WITH ENCRYPTION
AS
BEGIN
...
END

 

If somebody is trying to script your object will get something like that:

Encrypted Stored Procedure / Function

 

Nice isn't it? But, what happens if a large team is working on a product, company standard is to encrypt the stored procedures, but one of of the programmers forgot to check in the code for a couple of stored procedures in source control? People are making mistakes... it's human nature. Anyway, good news is that there are some stored procedures written, and free that can decrypt and repair those mistaken. I won't tell you where you can find them, just do a search on the web if you are interested in the subject.

The question is: it worth the effort to encrypt your stored procedures or not? If somebody want to get them is not a big problem. Even Microsoft documentation is saying that WITH ENCRYPTION 'obfuscating' not 'encrypting' the code. I'll let you judge what's the best solution.

 

Software - installing and re-instally over and over

by Andrei Hetel 17. April 2010 09:32

For a change, I won't write today about SQL Server - even if I've seen something very interesting this week. So, no code for today but I might come back to that some other time.

My main development tools are VS.NET 2005/2008 and SQL Server 2005/2008 - all installed on my good old laptop (named Ziggy) that I'm using at home. A while ago I start having problems with VS.NET 2008. First was crushing on every IDE close, but that didn't bother me too much... Unfortunately that was just the beggining. Next thing was to refuse to load packages related to Silverlight and WPF - and that was a problem for me!

All previous week I was trying to find a solution to avoid a full computer re-install. After every operation of un-install/install with service packs and windows updates and wasted time, a new set of packages (all the time different!) refuses to load. I searched the net right, left and center to find a solution, but none of them were applicable.

I reach a 2 conclusions after that long work of reading people installations problems:

  • Always install VS2005 and 2008 on C drive! Mine were installed on E, and I was having some surprises when looking in the registry! (I could see clearly that was trying to load some files located on C while they exist only on E) - that problem was surely caused by a windows update, (I have no idea which one) where a 'senior' programmer was doing a stupid hard-coding.
  • Try to do not install BETA software... 90% of solutions were explaining how to solve an issue in case you were brave enough to install whatever BETA

Anyway... last night I start re-installing everything from scratch. For me, is a pain - I can't understand people that enjoy this kind of work - and I know a couple of them. Actually, that was half of the reason for no code today :) - I'm not completely back; still installing while blogging.
But that wasn't all unfortunately. I have an external HDD, where I saved data from my laptop last night before re-installation. The big surprise comes in the morning when the partition from my backup disk was lost :(. That was too nice. In case you need to recover a partition try EASEUS Partition Master, was doing the job pretty quick in my case.

 

Row-by-row processing

by Andrei Hetel 11. April 2010 07:37

This week another "style" of coding kept my attention. Problem that people were trying to solve was a little bit more complex, but I'll build a simple example to illustrate the idea that I'm talking about today. We will define tables with minimal fields for 2 entities: Customers and Orders.

 

CREATE TABLE Orders
(
OrderId INT IDENTITY(1,1) PRIMARY KEY,
CustomerId INT,
OrderNumber VARCHAR(15),
OrderDate DATETIME,
OrderAmount NUMERIC(10,4)
)

CREATE TABLE Customers
(
CustomerId INT IDENTITY(1,1) PRIMARY KEY,
CustomerName VARCHAR(50),
CustomerAddress VARCHAR(255),
EmailAddress VARCHAR(255),
Active TINYINT
)

 

Business requirement was to insert into table MailShot email address of every customer that place an order with a delay no longer than 10 minutes. Mail shot table is used by another application that is responsible for sending welcome letters; definition of the table below.

 

CREATE TABLE MailShot
(
MailShotId INT IDENTITY(1,1) PRIMARY KEY,
EmailAddress VARCHAR(255)
)

 

That problem was solved in a very special way. A stored procedure (see below) was created and scheduled to run into a job step every 5 minute.

 

CREATE PROCEDURE PopulateMailShot
  (@LastOrderIdProcessed INT)
AS
BEGIN
  DECLARE @counter INT
  DECLARE @maxId INT
  DECLARE @cntOrders INT
  DECLARE @CustomerId INT
  DECLARE @EmailAddress VARCHAR(255)

  DECLARE @cl TABLE
    (id INT IDENTITY(1,1),
    CustomerId INT,
    EmailAddress VARCHAR(255)
  )

  INSERT INTO @cl
  SELECT CustomerId, EmailAddress
  FROM Customers
  WHERE Active = 1

  SELECT
    @counter = MIN(id),
    @maxId = MAX(id) + 1
  FROM @cl

  WHILE @counter < @maxId
    BEGIN

      SELECT
        @CustomerId = id, @EmailAddress=EmailAddress
      FROM @cl
      WHERE id = @counter

      SET @cntOrders=0
      SELECT @cntOrders=COUNT(*)
      FROM Orders
      WHERE CustomerId = @CustomerId
        AND OrderId > @LastOrderIdProcessed

      IF @cntOrders > 0
        BEGIN
          INSERT INTO MailShot (EmailAddress) VALUES (@EmailAddress)
        END

      SET @counter = @counter + 1
    END
END

 

Great isn't it? The most elegant way to solve such a problem is to use an insert trigger on Orders table. But there is also another way if the use of triggers in prohibited. Isn't it much more simple and efficient to solve everything from a single SQL statement instead of that crazy row-by-row processing? Procedure body, doing exactly the same thing like this:

 

INSERT INTO MailShot
(EmailAddress)
SELECT DISTINCT
  C.EmailAddress
FROM
  Orders O INNER JOIN Customers C
  ON O.CustomerId = C.CustomerId
WHERE O.OrderId > @LastOrderIdProcessed

 

SQL Server trigger

by Andrei Hetel 3. April 2010 10:06

Everybody knows what is a trigger, so I won't insist on this subject. My goal is to use a little example and show a mistake that I have seen too many times. Article is a little bit long, but easy to follow. So, let's suppose we are selling books, so the minimal definition of a Product table look like this:

 

CREATE TABLE Products (ProductID INT IDENTITY(1,1) PRIMARY KEY,
CategoryId INT,
ProductName VARCHAR(50),
Price NUMERIC(10,4)
)

 

Business requirement is to log all price changes into ProductPriceChanges table. Definition of the table follows.

 

CREATE TABLE ProductPriceChanges
(ProductPriceChange INT IDENTITY(1,1) PRIMARY KEY,
ProductID INT,
OldPrice NUMERIC(10,4),
NewPrice NUMERIC(10,4),
DateChanged DATETIME DEFAULT GETDATE()
)

 

Let's put some test data, suppose Category Id 1 means 'Bestsellers' and Category Id 2 means 'Comic Books'.

 

INSERT INTO Products (CategoryId, ProductName, Price)
SELECT 1, 'Theodore Boone, Kid Lawyer', '16.99'
UNION ALL
SELECT 1, 'The Red Pyramid', 17.99
UNION ALL
SELECT 2, 'Rodrick Rules', 13.95

 

We have tables, test data, let's create the trigger - this is the example of 'Don't do like this!'

 

CREATE TRIGGER PrUpdate ON dbo.Products AFTER UPDATE
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @ProductID INT
    DECLARE @OldPrice NUMERIC(10,4)
    DECLARE @NewPrice NUMERIC(10,4)

    SELECT @ProductID=ProductID, @OldPrice=Price FROM deleted
    SELECT @NewPrice=Price FROM inserted

    INSERT INTO ProductPriceChanges
    (ProductID, OldPrice, NewPrice)
    VALUES (@ProductID, @OldPrice, @NewPrice)
END

 

Let's test our trigger by issuing the following command:

 

UPDATE Products SET Price = 15.99 WHERE ProductId = 1

 

Excellent, it worked! See the picture below.

Trigger result

 

But, next day company management decide to reduce the price of all books from 'Bestsellers' category with 5%. Remember that in our test scenario there are 2 records. So, issue the following SQL statement:

 

UPDATE Products SET Price = Price * .95 WHERE CategoryId = 1

 

See the next image. Price change for product id 2 was not logged. That's because the way trigger is written (in Oracle there is clause - FOR EACH ROW that run the code for each individual row. In SQL Server there is no such option). So, we should rewrite the trigger in such a way that ALL the records affected by the update statement will be logged into ProductPriceChanges table.

 

Wrong trigger result

 

Modified version of the trigger:

ALTER TRIGGER PrUpdate ON dbo.Products AFTER UPDATE
AS
BEGIN


    SET NOCOUNT ON;

    INSERT INTO ProductPriceChanges
    (ProductID, OldPrice, NewPrice)
    SELECT d.ProductID, d.Price, i.Price
    FROM deleted d, inserted i
    WHERE d.ProductID = i.ProductID

END

 

Run the last update again and you'll notice that all price changes are reflected. Hope it helps.

 

About TempDb log

by Andrei Hetel 29. March 2010 03:17

TempDb is a system database available to all users and databases belonging to a SQL Server instance. As the name suggest is a temporary store. When TempDb LOG is growing too big or too fast (I've seen a log occupying 200GB - 97% full) obviously there is a problem with your database. Solutions that you may found on the net are:

 

  • Shrink tempDB transaction log - using log backup for example, inside a job
  • Restart SQL Server - that will recreate tempdb and tempdb log with default initial size

 

 

Both can be used just in the case you don't want to admit that your database has a problem. You should start looking first at the objects that generate excessive use of TempDb like:

 

 

  • Triggers - yes, because inserted and deleted tables are created in TempDB
  • Temporary Tables or Global Temporary Tables (e.g. CREATE TABLE #MyTemp(col1 INT)).
  • Table variables (DECLARE @MyTemp TABLE (col1 INT)) - Yes they can be created in TempDB!
  • XML - sp_xml_preparedocument creates a work table in tempdb.
  • Queries can store intermediate results for joins, aggregates (GROUP BY) and sorting (ORDER BY).
  • Cursors, where static type is the worse possible.

Note: Personally, I can't see any reason somebody would ever use a Global Temporary Table!

 

Use the following command to list all temp tables:

 

SELECT name FROM tempdb..sysobjects
WHERE NAME LIKE '#%'

 

Output, like in the following image:

TempDB Objects

 

Easiest method to monitor the logs is to issue the following command:

 

DBCC SQLPERF(LOGSPACE)

 

Result looks like in the following picture, you are interested on line 2 :

 

DBCC SQLPERF output

 

 

As a conclusion, I would recommend 2 things:

  • Review your code and try to reduce the use tempdb (see above who's using temp db).
  • Estimate the amount of space required by tempdb database, and set initial values for it - we don't want extents.