Erratic SMO Performance

I have written a console app which uses SMO to automate the scripting and the creating of our database.

For some strange reason the performance varies from blindingly fast to excruciatingly slow and I cannot seem to account for the cause. Can anyone tell me why?

For example, when scripting 100 tables, sometimes it will take 1.5 second to do all of them; sometimes it takes about 1.5 seconds to do each one!

Some debugging uncovered the fact that it is the scripting of DROP statements that is the culprit. Scripting a CREATE statment seems to take between 1 and 70ms, whereas scripting a DROP statement *sometimes* takes the same amount of time, but *sometimes* it takes about 1400ms!

I've tried varying all sorts of things but I can't reliably reproduce the problem. Indeed, when I was debugging it to get these metrics, performance suddenly bounced back and there was no longer a problem to debug. Nevertheless, the slow performance occurs frequently, if not more often than the fast performance. I thought it might be memory problems, but memory usage doesn't seem to be any different between high and low performance scenarios.

Obviously, there could be a better way of using SMO that would side step the problem completely, in which case I'm all ears. :-) Here is the code for scripting all tables to a file.

OnProgress(new ProgressEventArgs(""));

OnProgress(new ProgressEventArgs("Scripting tables..."));

ScriptingOptions scriptOptions = new ScriptingOptions();

scriptOptions.AppendToFile = true; // but file will be deleted first

scriptOptions.FileName = PathHelper.IncludeTrailingBackslash(options.Directory) + Constants.CreateTablesFileName;

scriptOptions.DriAll = false;

scriptOptions.ExtendedProperties = true;

scriptOptions.IncludeHeaders = false;

scriptOptions.Indexes = true;

scriptOptions.ClusteredIndexes = true;

scriptOptions.NonClusteredIndexes = true;

scriptOptions.ScriptDrops = true;

scriptOptions.Triggers = true;

scriptOptions.NoFileGroup = false;

DataTable dt = db.EnumObjects(DatabaseObjectTypes.Table, SortOrder.Name);

DataView view = dt.DefaultView;

view.RowFilter = "(Schema <> 'sys') AND (Schema <> 'INFORMATION_SCHEMA')";

numTables = view.Count;

numSuccessfulTables = 0;

File.Delete(scriptOptions.FileName);

using (StreamWriter writer = new StreamWriter(scriptOptions.FileName, false, new UnicodeEncoding()))

{

writer.WriteLine("USE " + db.Name);

writer.WriteLine("GO");

writer.WriteLine();

}

foreach (DataRowView row in view)

{

Table tbl = db.Tables[row["Name"].ToString(), row["Schema"].ToString()];

// script drop

scriptOptions.ScriptDrops = true;

scriptOptions.IncludeIfNotExists = true;

//ignore diagram stuff

if (tbl.Name == "sysdiagrams")

{

numTables--;

continue;

}

tbl.Script(scriptOptions);

// script create

scriptOptions.ScriptDrops = false;

scriptOptions.IncludeIfNotExists = false;

tbl.Script(scriptOptions);

numSuccessfulTables++;

OnProgress(new ProgressEventArgs(null));

}

Is anyone from MS able to comment on this?|||

I spent a lot of time researching the SMO issue and decided to share what I know and what I have done. Here is some of what I found:

1. It seems like there are very few options to speed up access to SQL Server via SMO objects, at the moment. Several people said they have reported this as a major issue to Microsoft.

2. SMO is used natively by SQL Server 2005, so using DMO or other methods are likely to be deprecated in the future. Use them with caution.

3. SMO Scripting queries SQL Server very excessively. This appears to be the major problem and I haven't found away around it. A likely culprit is that, by default, not all SMO object attributes are retrieved from SQL Server at once. Any properties that are used but not yet retrieved require a round trip to the server.

4. There does not appear to be a class or method which will allow you to script an entire server or database at one time. You need to script each object seperately.

Module sqlas

Private stringCollection As New StringCollection
Private scripter As New Scripter

Sub Main()

Try

Dim selectedServer As New Server("NameOfServer")

' The Server object's DefaultInitFields does not include the IsSystemObject property by default
' This caused a major perfomance hit. These methods cause the Server object to include
' the all properties for the selected DB objects.

selectedServer.SetDefaultInitFields(True)

'***** These method calls were being used to set InitFields seperately rather than all at once
'selectedServer.SetDefaultInitFields(GetType(User), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(Table), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(View), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(StoredProcedure), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(UserDefinedFunction), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(Trigger), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(Column), True)
'*******************************

' The Scripter requires a reference to the server selected for scripting.
scripter.Server = selectedServer

' Alters the Scripter.Options because they differ from the default.
scripter.Options.Permissions = True
scripter.Options.DriPrimaryKey = True
scripter.Options.IncludeIfNotExists = True
scripter.Options.NoCollation = True

Dim database As Database = selectedServer.Databases("SelectedDatabase")

'***** These calls decreased time by 50%, but it is still too slow
database.PrefetchObjects(GetType(Table))
database.PrefetchObjects(GetType(User))
database.PrefetchObjects(GetType(View))
database.PrefetchObjects(GetType(StoredProcedure))
database.PrefetchObjects(GetType(UserDefinedFunction))
'*************************

' Location to store the scripts
Using streamWriter As New StreamWriter("PathToWriteTo", False)
For Each user As User In database.Users
If (user.IsSystemObject = False) Then
ScriptSmoObject(New Urn() {user.Urn}, streamWriter)
End If
Next

' ROLES - Scripts Role objects
Console.ForegroundColor = ConsoleColor.Yellow
For Each databaseRole As DatabaseRole In database.Roles
If (databaseRole.Name.StartsWith("db_") = False) Then
ScriptSmoObject(New Urn() {databaseRole.Urn}, streamWriter)
End If
Next

' SCHEMAS - Scripts Schema objects
For Each schema As Schema In database.Schemas
ScriptSmoObject(New Urn() {schema.Urn}, streamWriter)
Next

' TABLES - Scripts Table objects
' The Scripter.Options.DriForeignKeys property is set to false so that FKs are not
' scripted with the table objects. They are scripted at the end of the file.
scripter.Options.DriForeignKeys = False
For Each table As Table In database.Tables
If (table.IsSystemObject = False) Then
ScriptSmoObject(New Urn() {table.Urn}, streamWriter)

' Table specific triggers will be scripted with their respective tables
For Each trigger As Trigger In table.Triggers
ScriptSmoObject(New Urn() {trigger.Urn}, streamWriter)
Next
End If
Next table

' VIEWS - Scripts View objects
For Each view As View In database.Views
If view.IsSystemObject = False Then
ScriptSmoObject(New Urn() {view.Urn}, streamWriter)
End If
Next view

' STORED PROCEDURES - Scripts StoredProcedure objects
For Each storedProcedure As StoredProcedure In database.StoredProcedures
If (storedProcedure.IsSystemObject = False) Then
ScriptSmoObject(New Urn() {storedProcedure.Urn}, streamWriter)
End If
Next storedProcedure

' USE DEFINED FUNCTIONS - Scripts UserDefinedFunction objects
For Each userDefinedFunction As UserDefinedFunction In database.UserDefinedFunctions
If (userDefinedFunction.IsSystemObject) = False Then
ScriptSmoObject(New Urn() {userDefinedFunction.Urn}, streamWriter)
End If
Next

' TRIGGERS - Scripts database level Trigger objects
For Each trigger As Trigger In database.Triggers
If trigger.IsSystemObject = False Then
ScriptSmoObject(New Urn() {trigger.Urn}, streamWriter)
End If
Next

' FOREIGN KEYS - Scripts the ForeignKey objects for all tables in the DB
' The Scripter.Options.DriForeignKeys property is set to true so that FKs will
' be scripted at the end of the file
scripter.Options.DriForeignKeys = True
For Each table As Table In database.Tables
For Each foreignKey As ForeignKey In table.ForeignKeys
ScriptSmoObject(New Urn() {foreignKey.Urn}, streamWriter)
Next

streamWriter.Flush()
End Using

Catch ex As Exception
Console.WriteLine(ex.Message)
End Try

End Sub

''' <summary>
''' Scripts an SMO object
''' </summary>
''' <param name="urn">The Urn of the SqlSmoObject to script</param>
''' <param name="streamWriter">The StreamWriter used to write the script to file.</param>
''' <remarks>None</remarks>
Private Sub ScriptSmoObject(ByRef urn() As Urn, ByRef streamWriter As TextWriter)
stringCollection = scripter.Script(urn)
For Each script As String In stringCollection
streamWriter.WriteLine(script)
Next

End Sub
End Module

Erratic SMO Performance

I have written a console app which uses SMO to automate the scripting and the creating of our database.

For some strange reason the performance varies from blindingly fast to excruciatingly slow and I cannot seem to account for the cause. Can anyone tell me why?

For example, when scripting 100 tables, sometimes it will take 1.5 second to do all of them; sometimes it takes about 1.5 seconds to do each one!

Some debugging uncovered the fact that it is the scripting of DROP statements that is the culprit. Scripting a CREATE statment seems to take between 1 and 70ms, whereas scripting a DROP statement *sometimes* takes the same amount of time, but *sometimes* it takes about 1400ms!

I've tried varying all sorts of things but I can't reliably reproduce the problem. Indeed, when I was debugging it to get these metrics, performance suddenly bounced back and there was no longer a problem to debug. Nevertheless, the slow performance occurs frequently, if not more often than the fast performance. I thought it might be memory problems, but memory usage doesn't seem to be any different between high and low performance scenarios.

Obviously, there could be a better way of using SMO that would side step the problem completely, in which case I'm all ears. :-) Here is the code for scripting all tables to a file.

OnProgress(new ProgressEventArgs(""));

OnProgress(new ProgressEventArgs("Scripting tables..."));

ScriptingOptions scriptOptions = new ScriptingOptions();

scriptOptions.AppendToFile = true; // but file will be deleted first

scriptOptions.FileName = PathHelper.IncludeTrailingBackslash(options.Directory) + Constants.CreateTablesFileName;

scriptOptions.DriAll = false;

scriptOptions.ExtendedProperties = true;

scriptOptions.IncludeHeaders = false;

scriptOptions.Indexes = true;

scriptOptions.ClusteredIndexes = true;

scriptOptions.NonClusteredIndexes = true;

scriptOptions.ScriptDrops = true;

scriptOptions.Triggers = true;

scriptOptions.NoFileGroup = false;

DataTable dt = db.EnumObjects(DatabaseObjectTypes.Table, SortOrder.Name);

DataView view = dt.DefaultView;

view.RowFilter = "(Schema <> 'sys') AND (Schema <> 'INFORMATION_SCHEMA')";

numTables = view.Count;

numSuccessfulTables = 0;

File.Delete(scriptOptions.FileName);

using (StreamWriter writer = new StreamWriter(scriptOptions.FileName, false, new UnicodeEncoding()))

{

writer.WriteLine("USE " + db.Name);

writer.WriteLine("GO");

writer.WriteLine();

}

foreach (DataRowView row in view)

{

Table tbl = db.Tables[row["Name"].ToString(), row["Schema"].ToString()];

// script drop

scriptOptions.ScriptDrops = true;

scriptOptions.IncludeIfNotExists = true;

//ignore diagram stuff

if (tbl.Name == "sysdiagrams")

{

numTables--;

continue;

}

tbl.Script(scriptOptions);

// script create

scriptOptions.ScriptDrops = false;

scriptOptions.IncludeIfNotExists = false;

tbl.Script(scriptOptions);

numSuccessfulTables++;

OnProgress(new ProgressEventArgs(null));

}

Is anyone from MS able to comment on this?|||

I spent a lot of time researching the SMO issue and decided to share what I know and what I have done. Here is some of what I found:

1. It seems like there are very few options to speed up access to SQL Server via SMO objects, at the moment. Several people said they have reported this as a major issue to Microsoft.

2. SMO is used natively by SQL Server 2005, so using DMO or other methods are likely to be deprecated in the future. Use them with caution.

3. SMO Scripting queries SQL Server very excessively. This appears to be the major problem and I haven't found away around it. A likely culprit is that, by default, not all SMO object attributes are retrieved from SQL Server at once. Any properties that are used but not yet retrieved require a round trip to the server.

4. There does not appear to be a class or method which will allow you to script an entire server or database at one time. You need to script each object seperately.

Module sqlas

Private stringCollection As New StringCollection
Private scripter As New Scripter

Sub Main()

Try

Dim selectedServer As New Server("NameOfServer")

' The Server object's DefaultInitFields does not include the IsSystemObject property by default
' This caused a major perfomance hit. These methods cause the Server object to include
' the all properties for the selected DB objects.

selectedServer.SetDefaultInitFields(True)

'***** These method calls were being used to set InitFields seperately rather than all at once
'selectedServer.SetDefaultInitFields(GetType(User), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(Table), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(View), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(StoredProcedure), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(UserDefinedFunction), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(Trigger), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(Column), True)
'*******************************

' The Scripter requires a reference to the server selected for scripting.
scripter.Server = selectedServer

' Alters the Scripter.Options because they differ from the default.
scripter.Options.Permissions = True
scripter.Options.DriPrimaryKey = True
scripter.Options.IncludeIfNotExists = True
scripter.Options.NoCollation = True

Dim database As Database = selectedServer.Databases("SelectedDatabase")

'***** These calls decreased time by 50%, but it is still too slow
database.PrefetchObjects(GetType(Table))
database.PrefetchObjects(GetType(User))
database.PrefetchObjects(GetType(View))
database.PrefetchObjects(GetType(StoredProcedure))
database.PrefetchObjects(GetType(UserDefinedFunction))
'*************************

' Location to store the scripts
Using streamWriter As New StreamWriter("PathToWriteTo", False)
For Each user As User In database.Users
If (user.IsSystemObject = False) Then
ScriptSmoObject(New Urn() {user.Urn}, streamWriter)
End If
Next

' ROLES - Scripts Role objects
Console.ForegroundColor = ConsoleColor.Yellow
For Each databaseRole As DatabaseRole In database.Roles
If (databaseRole.Name.StartsWith("db_") = False) Then
ScriptSmoObject(New Urn() {databaseRole.Urn}, streamWriter)
End If
Next

' SCHEMAS - Scripts Schema objects
For Each schema As Schema In database.Schemas
ScriptSmoObject(New Urn() {schema.Urn}, streamWriter)
Next

' TABLES - Scripts Table objects
' The Scripter.Options.DriForeignKeys property is set to false so that FKs are not
' scripted with the table objects. They are scripted at the end of the file.
scripter.Options.DriForeignKeys = False
For Each table As Table In database.Tables
If (table.IsSystemObject = False) Then
ScriptSmoObject(New Urn() {table.Urn}, streamWriter)

' Table specific triggers will be scripted with their respective tables
For Each trigger As Trigger In table.Triggers
ScriptSmoObject(New Urn() {trigger.Urn}, streamWriter)
Next
End If
Next table

' VIEWS - Scripts View objects
For Each view As View In database.Views
If view.IsSystemObject = False Then
ScriptSmoObject(New Urn() {view.Urn}, streamWriter)
End If
Next view

' STORED PROCEDURES - Scripts StoredProcedure objects
For Each storedProcedure As StoredProcedure In database.StoredProcedures
If (storedProcedure.IsSystemObject = False) Then
ScriptSmoObject(New Urn() {storedProcedure.Urn}, streamWriter)
End If
Next storedProcedure

' USE DEFINED FUNCTIONS - Scripts UserDefinedFunction objects
For Each userDefinedFunction As UserDefinedFunction In database.UserDefinedFunctions
If (userDefinedFunction.IsSystemObject) = False Then
ScriptSmoObject(New Urn() {userDefinedFunction.Urn}, streamWriter)
End If
Next

' TRIGGERS - Scripts database level Trigger objects
For Each trigger As Trigger In database.Triggers
If trigger.IsSystemObject = False Then
ScriptSmoObject(New Urn() {trigger.Urn}, streamWriter)
End If
Next

' FOREIGN KEYS - Scripts the ForeignKey objects for all tables in the DB
' The Scripter.Options.DriForeignKeys property is set to true so that FKs will
' be scripted at the end of the file
scripter.Options.DriForeignKeys = True
For Each table As Table In database.Tables
For Each foreignKey As ForeignKey In table.ForeignKeys
ScriptSmoObject(New Urn() {foreignKey.Urn}, streamWriter)
Next

streamWriter.Flush()
End Using

Catch ex As Exception
Console.WriteLine(ex.Message)
End Try

End Sub

''' <summary>
''' Scripts an SMO object
''' </summary>
''' <param name="urn">The Urn of the SqlSmoObject to script</param>
''' <param name="streamWriter">The StreamWriter used to write the script to file.</param>
''' <remarks>None</remarks>
Private Sub ScriptSmoObject(ByRef urn() As Urn, ByRef streamWriter As TextWriter)
stringCollection = scripter.Script(urn)
For Each script As String In stringCollection
streamWriter.WriteLine(script)
Next

End Sub
End Module

Erratic Performance on 5,000,000 records

Hi
I have a mssql 2000 database with 5 mil records in a table and the
performance seems to change from one day to the next from 5 sec result set
returns to 30sec. If i change the index sort order or sometimes move the
position of one of the lines in the "where" statement then it improves to 5
sec only to change the next day or two to 30 sec again.
Other variables like loading on the server, db etc are constant - it;s a
test system still so no users hitting DB.
Simple things like changing the position of the "null" condition below
improved things yesterday but today it's slow (3 sec to 30sec).
eg:
WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
worked well yesterday after being the other way around but now does not.
(This is for a param that could be passed thru as null or with a value)
Thanks
MikeHi,
According to information you provide i can only say that
WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
is not SARGABLE. It will perform Index Scan instead of Index Seek. If you're
index will grow or get fragmented you will have more costs.
Did you run this code in Stored Procedure?
I recommend you to use Dynamic SQL or if this is a SP then use wrapper sp
according to parameter passed to sp .
Many of the programmers using this kind of code but this type of usage is
easy to write for programmers but bad for performance.
Hope this helps.
"Mike C" wrote:

> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to
5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike
>
>|||Try using isnull(CL.DEPTCODE,'') = isnull(@.DEPTCODE,'')
"Mike C" wrote:

> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to
5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike
>
>|||Again this will perform index scan ..
(Assuming you have an index on DEPTCODE column)
"Saket" wrote:
[vbcol=seagreen]
> Try using isnull(CL.DEPTCODE,'') = isnull(@.DEPTCODE,'')
> "Mike C" wrote:
>|||If column DeptCode does not contain NULLs, then you can use the
following statement. It assumes DeptCode is of datatype varchar. If is
of a different data type, then post back.
WHERE CL.DeptCode LIKE COALESCE(@.DeptCode,'%')
Hope this helps,
Gert-Jan
Mike C wrote:
> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to
5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike

Erratic Performance on 5,000,000 records

Hi
I have a mssql 2000 database with 5 mil records in a table and the
performance seems to change from one day to the next from 5 sec result set
returns to 30sec. If i change the index sort order or sometimes move the
position of one of the lines in the "where" statement then it improves to 5
sec only to change the next day or two to 30 sec again.
Other variables like loading on the server, db etc are constant - it;s a
test system still so no users hitting DB.
Simple things like changing the position of the "null" condition below
improved things yesterday but today it's slow (3 sec to 30sec).
eg:
WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
worked well yesterday after being the other way around but now does not.
(This is for a param that could be passed thru as null or with a value)
Thanks
Mike
Hi,
According to information you provide i can only say that
WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
is not SARGABLE. It will perform Index Scan instead of Index Seek. If you're
index will grow or get fragmented you will have more costs.
Did you run this code in Stored Procedure?
I recommend you to use Dynamic SQL or if this is a SP then use wrapper sp
according to parameter passed to sp .
Many of the programmers using this kind of code but this type of usage is
easy to write for programmers but bad for performance.
Hope this helps.
"Mike C" wrote:

> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to 5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike
>
>
|||Try using isnull(CL.DEPTCODE,'') = isnull(@.DEPTCODE,'')
"Mike C" wrote:

> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to 5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike
>
>
|||Again this will perform index scan ..
(Assuming you have an index on DEPTCODE column)
"Saket" wrote:
[vbcol=seagreen]
> Try using isnull(CL.DEPTCODE,'') = isnull(@.DEPTCODE,'')
> "Mike C" wrote:
|||If column DeptCode does not contain NULLs, then you can use the
following statement. It assumes DeptCode is of datatype varchar. If is
of a different data type, then post back.
WHERE CL.DeptCode LIKE COALESCE(@.DeptCode,'%')
Hope this helps,
Gert-Jan
Mike C wrote:
> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to 5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike

Erratic Performance on 5,000,000 records

Hi
I have a mssql 2000 database with 5 mil records in a table and the
performance seems to change from one day to the next from 5 sec result set
returns to 30sec. If i change the index sort order or sometimes move the
position of one of the lines in the "where" statement then it improves to 5
sec only to change the next day or two to 30 sec again.
Other variables like loading on the server, db etc are constant - it;s a
test system still so no users hitting DB.
Simple things like changing the position of the "null" condition below
improved things yesterday but today it's slow (3 sec to 30sec).
eg:
WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
worked well yesterday after being the other way around but now does not.
(This is for a param that could be passed thru as null or with a value)
Thanks
MikeHi,
According to information you provide i can only say that
WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
is not SARGABLE. It will perform Index Scan instead of Index Seek. If you're
index will grow or get fragmented you will have more costs.
Did you run this code in Stored Procedure?
I recommend you to use Dynamic SQL or if this is a SP then use wrapper sp
according to parameter passed to sp .
Many of the programmers using this kind of code but this type of usage is
easy to write for programmers but bad for performance.
Hope this helps.
"Mike C" wrote:
> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to 5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike
>
>|||Try using isnull(CL.DEPTCODE,'') = isnull(@.DEPTCODE,'')
"Mike C" wrote:
> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to 5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike
>
>|||Again this will perform index scan ..
(Assuming you have an index on DEPTCODE column)
"Saket" wrote:
> Try using isnull(CL.DEPTCODE,'') = isnull(@.DEPTCODE,'')
> "Mike C" wrote:
> > Hi
> >
> > I have a mssql 2000 database with 5 mil records in a table and the
> > performance seems to change from one day to the next from 5 sec result set
> > returns to 30sec. If i change the index sort order or sometimes move the
> > position of one of the lines in the "where" statement then it improves to 5
> > sec only to change the next day or two to 30 sec again.
> >
> > Other variables like loading on the server, db etc are constant - it;s a
> > test system still so no users hitting DB.
> >
> > Simple things like changing the position of the "null" condition below
> > improved things yesterday but today it's slow (3 sec to 30sec).
> >
> > eg:
> >
> > WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> >
> > worked well yesterday after being the other way around but now does not.
> > (This is for a param that could be passed thru as null or with a value)
> >
> > Thanks
> >
> > Mike
> >
> >
> >|||If column DeptCode does not contain NULLs, then you can use the
following statement. It assumes DeptCode is of datatype varchar. If is
of a different data type, then post back.
WHERE CL.DeptCode LIKE COALESCE(@.DeptCode,'%')
Hope this helps,
Gert-Jan
Mike C wrote:
> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to 5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike

Erratic Performance of SQL Server

Hello,

We have a complex system with many stored procedures, the same procedures run every day at about the same time. We are noticing severe fluctuations in performance. One day a procedure will take 150 minutes, the next day 250 minutes, the next day 100 minutes. A graph of the performance looks like a voice graph or a lie detector for a criminal. We are using SQL Server 2000 on an Itanium. Any suggestions or hints about how to stablize? This is happening for all of our procedures that run 24 hours a day.

Hi.

Are you running with SQL Server 2000 service pack 4 (for build 2039) and the last 2000 cumulative hotfix package (for build 2187)?

Regards,

Gary.

|||Stored procedure performance is always going to be directly relative to the load on the server, the number of rows being processed, the nature of the queries, i.e. read only versus updates and, in particular, on the performance of the I/O devices which can certainly vary if your stored procedures are competing against other processes trying to access the same data.

What is the stored procedure doing and what kind of data volumes are we talking about, i.e. a thousand rows, 10 million rows? Are there any cursors? Are there any other applications competing for the same data, i.e. an OLTP system?
|||

You might want to take the following steps:

1. Check the DBCC SHOW_STATISTICS and rowmodctr values (under the sysindexes table -- The rowmodctr value should be as close to ZERO as possible) and find out if the statistics for the database and the tables involved are out of date. If yes, please update the statistics with a full scan or 100% sampling rate.

2. Make sure that you are not running into a parameter sniffing issue. Please refer the following article for more details:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

http://msdn2.microsoft.com/en-us/library/ms190439.aspx

Sporadic bursts in SP performance which leads to inconsistent duration for a stored procedure would be due to recompilation of the SP with a bad input parameter which generates a bad plan.

Also, try and see if recompiling the stored procedure at that particular time helps.

|||You need to itemize your durations. If you run Profiler, you will see real execution costs, such as amount of CPU and number of reads/writes. Are these values wildly different from day to day? Note that different durations may be caused by:
- locking, when you procedure spends significant time in lock waiting state;
- network delays. Your procedure may be waiting for the client to receive the result sets.

For instance, you can run one and the same query twice and get the same CPU, the same reads, but very different duration, because one time there were no exclusive locks to wait for, and the other time the procedure spent most of the time in lock ewaiting state. Another example is when you invoke a query from SSMS, and most of the duration is spent by SSMS drawing a grid. If you switch to text mode and rerun the query, you may get a shorter duration.|||

Thanks for your replies, I'll look at these in more detail, but I want to provide some additional information.

We are running SQL Server 2000, Enterprise edition, 64-bit, version 8.00.2039 SP4. Our server is not shared by any other application, but we have two instances with concurrent processing. The two instances use separate databases, but each instance has 3 job queues that can run procesing tasks simultaneously. Locking could be an issue on one instance, but not on the other instance. We are observing the same erratic processing times on both instances. Our SQL server uses a SAN, which is used by other applications, but the processing performed by the other applications is very light. We have several different procedures, most are not parameterized. Some of our longest running stored procedures perform a fairly simple SELECT INTO with joins on master data tables -- but these procedures are the most erratic with regard to different processing times. The data volumes for these procedures is up to about 20 million records. Locking is not an issue in these procedures. Since all of our procedures seem to be erratic, I'm wondering if there might be a problem with the SQL server optimizer in our configuration.

|||Have you monitored your TempDB's? If they're too small, it will have to keep extending which is a serious drag on performance. If your stored procedures are doing a lot of sorts, group by's, and are using a lot of temp tables this might add further credence to the TempDB issue. Since you're using SELECT INTO the operations are unlogged so I doubt that's your problem. You also might monitor your QIO's on your SAN devices to see if there is a lot of contention, particularly on the device with TempDB on it. (If at all possible you might move the TempDB's to their own devices.) You might also consider creating one TempDB file for each processor if you haven't already and turning on Traceflag 1118.

Just some thoughts. Hope it helps.

Erratic Performance of SQL Server

Hello,

We have a complex system with many stored procedures, the same procedures run every day at about the same time. We are noticing severe fluctuations in performance. One day a procedure will take 150 minutes, the next day 250 minutes, the next day 100 minutes. A graph of the performance looks like a voice graph or a lie detector for a criminal. We are using SQL Server 2000 on an Itanium. Any suggestions or hints about how to stablize? This is happening for all of our procedures that run 24 hours a day.

Hi.

Are you running with SQL Server 2000 service pack 4 (for build 2039) and the last 2000 cumulative hotfix package (for build 2187)?

Regards,

Gary.

|||Stored procedure performance is always going to be directly relative to the load on the server, the number of rows being processed, the nature of the queries, i.e. read only versus updates and, in particular, on the performance of the I/O devices which can certainly vary if your stored procedures are competing against other processes trying to access the same data.

What is the stored procedure doing and what kind of data volumes are we talking about, i.e. a thousand rows, 10 million rows? Are there any cursors? Are there any other applications competing for the same data, i.e. an OLTP system?
|||

You might want to take the following steps:

1. Check the DBCC SHOW_STATISTICS and rowmodctr values (under the sysindexes table -- The rowmodctr value should be as close to ZERO as possible) and find out if the statistics for the database and the tables involved are out of date. If yes, please update the statistics with a full scan or 100% sampling rate.

2. Make sure that you are not running into a parameter sniffing issue. Please refer the following article for more details:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

http://msdn2.microsoft.com/en-us/library/ms190439.aspx

Sporadic bursts in SP performance which leads to inconsistent duration for a stored procedure would be due to recompilation of the SP with a bad input parameter which generates a bad plan.

Also, try and see if recompiling the stored procedure at that particular time helps.

|||You need to itemize your durations. If you run Profiler, you will see real execution costs, such as amount of CPU and number of reads/writes. Are these values wildly different from day to day? Note that different durations may be caused by:
- locking, when you procedure spends significant time in lock waiting state;
- network delays. Your procedure may be waiting for the client to receive the result sets.

For instance, you can run one and the same query twice and get the same CPU, the same reads, but very different duration, because one time there were no exclusive locks to wait for, and the other time the procedure spent most of the time in lock ewaiting state. Another example is when you invoke a query from SSMS, and most of the duration is spent by SSMS drawing a grid. If you switch to text mode and rerun the query, you may get a shorter duration.|||

Thanks for your replies, I'll look at these in more detail, but I want to provide some additional information.

We are running SQL Server 2000, Enterprise edition, 64-bit, version 8.00.2039 SP4. Our server is not shared by any other application, but we have two instances with concurrent processing. The two instances use separate databases, but each instance has 3 job queues that can run procesing tasks simultaneously. Locking could be an issue on one instance, but not on the other instance. We are observing the same erratic processing times on both instances. Our SQL server uses a SAN, which is used by other applications, but the processing performed by the other applications is very light. We have several different procedures, most are not parameterized. Some of our longest running stored procedures perform a fairly simple SELECT INTO with joins on master data tables -- but these procedures are the most erratic with regard to different processing times. The data volumes for these procedures is up to about 20 million records. Locking is not an issue in these procedures. Since all of our procedures seem to be erratic, I'm wondering if there might be a problem with the SQL server optimizer in our configuration.

|||Have you monitored your TempDB's? If they're too small, it will have to keep extending which is a serious drag on performance. If your stored procedures are doing a lot of sorts, group by's, and are using a lot of temp tables this might add further credence to the TempDB issue. Since you're using SELECT INTO the operations are unlogged so I doubt that's your problem. You also might monitor your QIO's on your SAN devices to see if there is a lot of contention, particularly on the device with TempDB on it. (If at all possible you might move the TempDB's to their own devices.) You might also consider creating one TempDB file for each processor if you haven't already and turning on Traceflag 1118.

Just some thoughts. Hope it helps.

Erratic Performance of SQL Server

Hello,

We have a complex system with many stored procedures, the same procedures run every day at about the same time. We are noticing severe fluctuations in performance. One day a procedure will take 150 minutes, the next day 250 minutes, the next day 100 minutes. A graph of the performance looks like a voice graph or a lie detector for a criminal. We are using SQL Server 2000 on an Itanium. Any suggestions or hints about how to stablize? This is happening for all of our procedures that run 24 hours a day.

Hi.

Are you running with SQL Server 2000 service pack 4 (for build 2039) and the last 2000 cumulative hotfix package (for build 2187)?

Regards,

Gary.

|||Stored procedure performance is always going to be directly relative to the load on the server, the number of rows being processed, the nature of the queries, i.e. read only versus updates and, in particular, on the performance of the I/O devices which can certainly vary if your stored procedures are competing against other processes trying to access the same data.

What is the stored procedure doing and what kind of data volumes are we talking about, i.e. a thousand rows, 10 million rows? Are there any cursors? Are there any other applications competing for the same data, i.e. an OLTP system?
|||

You might want to take the following steps:

1. Check the DBCC SHOW_STATISTICS and rowmodctr values (under the sysindexes table -- The rowmodctr value should be as close to ZERO as possible) and find out if the statistics for the database and the tables involved are out of date. If yes, please update the statistics with a full scan or 100% sampling rate.

2. Make sure that you are not running into a parameter sniffing issue. Please refer the following article for more details:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

http://msdn2.microsoft.com/en-us/library/ms190439.aspx

Sporadic bursts in SP performance which leads to inconsistent duration for a stored procedure would be due to recompilation of the SP with a bad input parameter which generates a bad plan.

Also, try and see if recompiling the stored procedure at that particular time helps.

|||You need to itemize your durations. If you run Profiler, you will see real execution costs, such as amount of CPU and number of reads/writes. Are these values wildly different from day to day? Note that different durations may be caused by:
- locking, when you procedure spends significant time in lock waiting state;
- network delays. Your procedure may be waiting for the client to receive the result sets.

For instance, you can run one and the same query twice and get the same CPU, the same reads, but very different duration, because one time there were no exclusive locks to wait for, and the other time the procedure spent most of the time in lock ewaiting state. Another example is when you invoke a query from SSMS, and most of the duration is spent by SSMS drawing a grid. If you switch to text mode and rerun the query, you may get a shorter duration.|||

Thanks for your replies, I'll look at these in more detail, but I want to provide some additional information.

We are running SQL Server 2000, Enterprise edition, 64-bit, version 8.00.2039 SP4. Our server is not shared by any other application, but we have two instances with concurrent processing. The two instances use separate databases, but each instance has 3 job queues that can run procesing tasks simultaneously. Locking could be an issue on one instance, but not on the other instance. We are observing the same erratic processing times on both instances. Our SQL server uses a SAN, which is used by other applications, but the processing performed by the other applications is very light. We have several different procedures, most are not parameterized. Some of our longest running stored procedures perform a fairly simple SELECT INTO with joins on master data tables -- but these procedures are the most erratic with regard to different processing times. The data volumes for these procedures is up to about 20 million records. Locking is not an issue in these procedures. Since all of our procedures seem to be erratic, I'm wondering if there might be a problem with the SQL server optimizer in our configuration.

|||Have you monitored your TempDB's? If they're too small, it will have to keep extending which is a serious drag on performance. If your stored procedures are doing a lot of sorts, group by's, and are using a lot of temp tables this might add further credence to the TempDB issue. Since you're using SELECT INTO the operations are unlogged so I doubt that's your problem. You also might monitor your QIO's on your SAN devices to see if there is a lot of contention, particularly on the device with TempDB on it. (If at all possible you might move the TempDB's to their own devices.) You might also consider creating one TempDB file for each processor if you haven't already and turning on Traceflag 1118.

Just some thoughts. Hope it helps.