Collation name mismatch - .NET SqlException

I've been doing some minor mods for my client and he's reported a
weird error. Weird, because until recently all was going swimmingly.
It's a Visual Studio .NET 2003 ASP.NET/VB.NET application with a SQL
2000 database.
I couldn't reproduce the problem at my office so I asked them to
provide me with a backup, which I duly restored to a new test
database. Lo and behold, it failed with the same error:
[SqlException: Error converting data type varchar to datetime.]
Microsoft.VisualBasic.CompilerServices.LateBinding .InternalLateCall(Object
o, Type objType, String name, Object[] args, String[] paramnames,
Boolean[] CopyBack, Boolean IgnoreReturn)
Microsoft.VisualBasic.CompilerServices.LateBinding .LateCall(Object
o, Type objType, String name, Object[] args, String[] paramnames,
Boolean[] CopyBack)
However, looking at the tables and stored procedures involved I could
see no differences between my development database and their test
version (table defs and SPROCs all identical).
However, I did note that looking at the properties of the two
databases:
Development DB (no problems) - Collation Name =
SQL_Latin1_General_CP1_Cl_AS
Test DB (SqlException) - Collation Name =
There was no Collation Name.
I realise that there's no way of changing the Collation Name of an
existing database, but if I were to get the client to create a new
blank database with the same Collation Name as the development DB and
then restore the test db over it, would that work, do you think?
Thanks
Edward
> I realise that there's no way of changing the Collation Name of an
> existing database, but if I were to get the client to create a new
> blank database with the same Collation Name as the development DB and
> then restore the test db over it, would that work, do you think?
You can change the database default collation with ALTER DATABASE:
ALTER DATABASE MyDatabase
COLLATE SQL_Latin1_General_CP1_CI_AS;
Hope this helps.
Dan Guzman
SQL Server MVP
<teddysnips@.hotmail.com> wrote in message
news:2ad498ef-fd23-4f18-9c07-81ce8bdd40ac@.s19g2000prg.googlegroups.com...
> I've been doing some minor mods for my client and he's reported a
> weird error. Weird, because until recently all was going swimmingly.
> It's a Visual Studio .NET 2003 ASP.NET/VB.NET application with a SQL
> 2000 database.
> I couldn't reproduce the problem at my office so I asked them to
> provide me with a backup, which I duly restored to a new test
> database. Lo and behold, it failed with the same error:
> [SqlException: Error converting data type varchar to datetime.]
> Microsoft.VisualBasic.CompilerServices.LateBinding .InternalLateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack, Boolean IgnoreReturn)
> Microsoft.VisualBasic.CompilerServices.LateBinding .LateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack)
> However, looking at the tables and stored procedures involved I could
> see no differences between my development database and their test
> version (table defs and SPROCs all identical).
> However, I did note that looking at the properties of the two
> databases:
> Development DB (no problems) - Collation Name =
> SQL_Latin1_General_CP1_Cl_AS
> Test DB (SqlException) - Collation Name =
> There was no Collation Name.
> I realise that there's no way of changing the Collation Name of an
> existing database, but if I were to get the client to create a new
> blank database with the same Collation Name as the development DB and
> then restore the test db over it, would that work, do you think?
> Thanks
> Edward
|||> [SqlException: Error converting data type varchar to datetime.]
I forgot to add that this error is not related to collation. It looks like
varchar data is somewhere being converted to a datetime and the data isn't a
valid datetime string. You can identify problem data with ISDATE:
SELECT *
FROM dbo.MyTable
WHERE ISDATE(MyColumn) = 0
Hope this helps.
Dan Guzman
SQL Server MVP
<teddysnips@.hotmail.com> wrote in message
news:2ad498ef-fd23-4f18-9c07-81ce8bdd40ac@.s19g2000prg.googlegroups.com...
> I've been doing some minor mods for my client and he's reported a
> weird error. Weird, because until recently all was going swimmingly.
> It's a Visual Studio .NET 2003 ASP.NET/VB.NET application with a SQL
> 2000 database.
> I couldn't reproduce the problem at my office so I asked them to
> provide me with a backup, which I duly restored to a new test
> database. Lo and behold, it failed with the same error:
> [SqlException: Error converting data type varchar to datetime.]
> Microsoft.VisualBasic.CompilerServices.LateBinding .InternalLateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack, Boolean IgnoreReturn)
> Microsoft.VisualBasic.CompilerServices.LateBinding .LateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack)
> However, looking at the tables and stored procedures involved I could
> see no differences between my development database and their test
> version (table defs and SPROCs all identical).
> However, I did note that looking at the properties of the two
> databases:
> Development DB (no problems) - Collation Name =
> SQL_Latin1_General_CP1_Cl_AS
> Test DB (SqlException) - Collation Name =
> There was no Collation Name.
> I realise that there's no way of changing the Collation Name of an
> existing database, but if I were to get the client to create a new
> blank database with the same Collation Name as the development DB and
> then restore the test db over it, would that work, do you think?
> Thanks
> Edward
sqlsql

Collation name mismatch - .NET SqlException

I've been doing some minor mods for my client and he's reported a
weird error. Weird, because until recently all was going swimmingly.
It's a Visual Studio .NET 2003 ASP.NET/VB.NET application with a SQL
2000 database.
I couldn't reproduce the problem at my office so I asked them to
provide me with a backup, which I duly restored to a new test
database. Lo and behold, it failed with the same error:
[SqlException: Error converting data type varchar to datetime.]
Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object
o, Type objType, String name, Object[] args, String[] paramnames,
Boolean[] CopyBack, Boolean IgnoreReturn)
Microsoft.VisualBasic.CompilerServices.LateBinding.LateCall(Object
o, Type objType, String name, Object[] args, String[] paramnames,
Boolean[] CopyBack)
However, looking at the tables and stored procedures involved I could
see no differences between my development database and their test
version (table defs and SPROCs all identical).
However, I did note that looking at the properties of the two
databases:
Development DB (no problems) - Collation Name = SQL_Latin1_General_CP1_Cl_AS
Test DB (SqlException) - Collation Name =
There was no Collation Name.
I realise that there's no way of changing the Collation Name of an
existing database, but if I were to get the client to create a new
blank database with the same Collation Name as the development DB and
then restore the test db over it, would that work, do you think?
Thanks
Edward> I realise that there's no way of changing the Collation Name of an
> existing database, but if I were to get the client to create a new
> blank database with the same Collation Name as the development DB and
> then restore the test db over it, would that work, do you think?
You can change the database default collation with ALTER DATABASE:
ALTER DATABASE MyDatabase
COLLATE SQL_Latin1_General_CP1_CI_AS;
--
Hope this helps.
Dan Guzman
SQL Server MVP
<teddysnips@.hotmail.com> wrote in message
news:2ad498ef-fd23-4f18-9c07-81ce8bdd40ac@.s19g2000prg.googlegroups.com...
> I've been doing some minor mods for my client and he's reported a
> weird error. Weird, because until recently all was going swimmingly.
> It's a Visual Studio .NET 2003 ASP.NET/VB.NET application with a SQL
> 2000 database.
> I couldn't reproduce the problem at my office so I asked them to
> provide me with a backup, which I duly restored to a new test
> database. Lo and behold, it failed with the same error:
> [SqlException: Error converting data type varchar to datetime.]
> Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack, Boolean IgnoreReturn)
> Microsoft.VisualBasic.CompilerServices.LateBinding.LateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack)
> However, looking at the tables and stored procedures involved I could
> see no differences between my development database and their test
> version (table defs and SPROCs all identical).
> However, I did note that looking at the properties of the two
> databases:
> Development DB (no problems) - Collation Name => SQL_Latin1_General_CP1_Cl_AS
> Test DB (SqlException) - Collation Name => There was no Collation Name.
> I realise that there's no way of changing the Collation Name of an
> existing database, but if I were to get the client to create a new
> blank database with the same Collation Name as the development DB and
> then restore the test db over it, would that work, do you think?
> Thanks
> Edward|||> [SqlException: Error converting data type varchar to datetime.]
I forgot to add that this error is not related to collation. It looks like
varchar data is somewhere being converted to a datetime and the data isn't a
valid datetime string. You can identify problem data with ISDATE:
SELECT *
FROM dbo.MyTable
WHERE ISDATE(MyColumn) = 0
--
Hope this helps.
Dan Guzman
SQL Server MVP
<teddysnips@.hotmail.com> wrote in message
news:2ad498ef-fd23-4f18-9c07-81ce8bdd40ac@.s19g2000prg.googlegroups.com...
> I've been doing some minor mods for my client and he's reported a
> weird error. Weird, because until recently all was going swimmingly.
> It's a Visual Studio .NET 2003 ASP.NET/VB.NET application with a SQL
> 2000 database.
> I couldn't reproduce the problem at my office so I asked them to
> provide me with a backup, which I duly restored to a new test
> database. Lo and behold, it failed with the same error:
> [SqlException: Error converting data type varchar to datetime.]
> Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack, Boolean IgnoreReturn)
> Microsoft.VisualBasic.CompilerServices.LateBinding.LateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack)
> However, looking at the tables and stored procedures involved I could
> see no differences between my development database and their test
> version (table defs and SPROCs all identical).
> However, I did note that looking at the properties of the two
> databases:
> Development DB (no problems) - Collation Name => SQL_Latin1_General_CP1_Cl_AS
> Test DB (SqlException) - Collation Name => There was no Collation Name.
> I realise that there's no way of changing the Collation Name of an
> existing database, but if I were to get the client to create a new
> blank database with the same Collation Name as the development DB and
> then restore the test db over it, would that work, do you think?
> Thanks
> Edward

collation & compatibility issues

1) how to check the colllation settings on SQL 7 ?
2) I have 2 SQL Srv - SQL7 and SQL2000.
A client application does the following:
- retrieves data from SQL2000 to the SQL7 for queries.
- data updated in SQL7 is also periodically syn with the SQL2000 srv.
there is no direct user acess with the tables, all is interfaced thru the
client app. So must the SQL Server 2000 compatibility settings (under server
properties) be changed to 7.0 ?
Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
SQL2000 from being accessed by other app.
The WAIT TYPE is usually networkIO for the blocking connection from SQL7 to
SQL2000.
So I suspect could it be collation settings or some other in-compatibility
between the 2 SQL Servers.
>
> 1) how to check the collation settings on SQL 7 ?
The error log will tell you this information.

> 2) I have 2 SQL Srv - SQL7 and SQL2000.
> A client application does the following:
> - retrieves data from SQL2000 to the SQL7 for queries.
> - data updated in SQL7 is also periodically syn with the SQL2000 srv.
> there is no direct user acess with the tables, all is interfaced thru the
> client app. So must the SQL Server 2000 compatibility settings (under
server
> properties) be changed to 7.0 ?
> Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
> SQL2000 from being accessed by other app.
> The WAIT TYPE is usually networkIO for the blocking connection from SQL7
to
> SQL2000.
> So I suspect could it be collation settings or some other in-compatibility
> between the 2 SQL Servers.
You should run a blocker script to identify the top of blocking chains. The
script from this article might help:
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Regards,
Eric Crdenas
SQL Server senior support professional

collation & compatibility issues

1) how to check the colllation settings on SQL 7 ?
2) I have 2 SQL Srv - SQL7 and SQL2000.
A client application does the following:
- retrieves data from SQL2000 to the SQL7 for queries.
- data updated in SQL7 is also periodically syn with the SQL2000 srv.
there is no direct user acess with the tables, all is interfaced thru the
client app. So must the SQL Server 2000 compatibility settings (under server
properties) be changed to 7.0 ?
Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
SQL2000 from being accessed by other app.
The WAIT TYPE is usually networkIO for the blocking connection from SQL7 to
SQL2000.
So I suspect could it be collation settings or some other in-compatibility
between the 2 SQL Servers.>
> 1) how to check the collation settings on SQL 7 ?
--
The error log will tell you this information.

> 2) I have 2 SQL Srv - SQL7 and SQL2000.
> A client application does the following:
> - retrieves data from SQL2000 to the SQL7 for queries.
> - data updated in SQL7 is also periodically syn with the SQL2000 srv.
> there is no direct user acess with the tables, all is interfaced thru the
> client app. So must the SQL Server 2000 compatibility settings (under
server
> properties) be changed to 7.0 ?
> Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
> SQL2000 from being accessed by other app.
> The WAIT TYPE is usually networkIO for the blocking connection from SQL7
to
> SQL2000.
> So I suspect could it be collation settings or some other in-compatibility
> between the 2 SQL Servers.
--
You should run a blocker script to identify the top of blocking chains. The
script from this article might help:
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Regards,
Eric Crdenas
SQL Server senior support professional

collation & compatibility issues

1) how to check the colllation settings on SQL 7 ?
2) I have 2 SQL Srv - SQL7 and SQL2000.
A client application does the following:
- retrieves data from SQL2000 to the SQL7 for queries.
- data updated in SQL7 is also periodically syn with the SQL2000 srv.
there is no direct user acess with the tables, all is interfaced thru the
client app. So must the SQL Server 2000 compatibility settings (under server
properties) be changed to 7.0 ?
Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
SQL2000 from being accessed by other app.
The WAIT TYPE is usually networkIO for the blocking connection from SQL7 to
SQL2000.
So I suspect could it be collation settings or some other in-compatibility
between the 2 SQL Servers.>
> 1) how to check the collation settings on SQL 7 ?
--
The error log will tell you this information.
> 2) I have 2 SQL Srv - SQL7 and SQL2000.
> A client application does the following:
> - retrieves data from SQL2000 to the SQL7 for queries.
> - data updated in SQL7 is also periodically syn with the SQL2000 srv.
> there is no direct user acess with the tables, all is interfaced thru the
> client app. So must the SQL Server 2000 compatibility settings (under
server
> properties) be changed to 7.0 ?
> Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
> SQL2000 from being accessed by other app.
> The WAIT TYPE is usually networkIO for the blocking connection from SQL7
to
> SQL2000.
> So I suspect could it be collation settings or some other in-compatibility
> between the 2 SQL Servers.
--
You should run a blocker script to identify the top of blocking chains. The
script from this article might help:
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Regards,
--
Eric Cárdenas
SQL Server senior support professional

Cognos and SQL Server 2005 Reporting Services

Hi,

I would like to get the differences between Cognos and SQL Server 2005 Reporting Services.One of the client is planning to Use Cognos and I am supporting SQL server 2005 Reporting Services.

Details regarding the pros and cons of SQL Server 2005 Reporting Services would be great.

I am looking for the details like this.

Cost of Cognos licencing Vs SQL Server Reporting services licensing?

Usability issues?,etc.

Any link to the comparision study between SQL server Reporting services and Cognos?

Thanks!

Raju

http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,sid63_gci1141747,00.html

try this link

code pages mess

Hi,
I have inherited a problem with code pages. When the server has a
different code page to the client the unrecognised characters are being
passed across as a question mark - ?.
I believe this is because SQL Server tries to translate it to the code
page it is using but can't find a match.
Is there a way to stop this attempted translation so that it just
passes it over as the character it is?
I'm not really up with different character sets and what is possible.
But the only reliable way to stop it is to make sure both client and
server are using the same code page.
Thank you.Hi
Displaying an unknown character as a question mark is usually a feature of
the client tools and not the server. If you check out the character value in
the database and on the client you will find they are the same.
John
"Feldaspar" wrote:

> Hi,
> I have inherited a problem with code pages. When the server has a
> different code page to the client the unrecognised characters are being
> passed across as a question mark - ?.
> I believe this is because SQL Server tries to translate it to the code
> page it is using but can't find a match.
> Is there a way to stop this attempted translation so that it just
> passes it over as the character it is?
> I'm not really up with different character sets and what is possible.
> But the only reliable way to stop it is to make sure both client and
> server are using the same code page.
> Thank you.
>|||No they are different,
It appears to be a bug with the OLEDB driver, see
http://support.microsoft.com/defaul...kb;en-us;234748
John Bell wrote:
[vbcol=seagreen]
> Hi
> Displaying an unknown character as a question mark is usually a feature of
> the client tools and not the server. If you check out the character value
in
> the database and on the client you will find they are the same.
> John
> "Feldaspar" wrote:
>|||Hi
The OLEDB driver is a client component! Have you checked whether you are
up-to-date with MDAC using the MDAC checker and load a more recent version i
f
inconsistent or old http://msdn.microsoft.com/data/ref/mdac/downloads/
John
"Feldaspar" wrote:

> No they are different,
> It appears to be a bug with the OLEDB driver, see
> http://support.microsoft.com/defaul...kb;en-us;234748
>
> John Bell wrote:
>
>|||Taken from
http://msdn.microsoft.com/library/d...ataencoding.asp
Disabling the AutoTranslate feature of the SQL Server ODBC driver to
insert data defined with a different code page from the server is not
supported. Also, even if AutoTranslate is disabled, it does not prevent
code page translation for SQL language events. The result is that if
the client and database code pages do not match, code page translation
will generally be applied to any non-Unicode character string sent to
or from the server. (For more information about AutoTranslate, see
Autotranslation of Character Data and PRB: SQL Server ODBC Driver
Converts Language Events to Unicode.
If possible, you should avoid this situation. The best choice for a
code page-specific server is to communicate only with clients using the
same code page. The second-best choice is to use another code page that
has an almost identical character set. For example, code page 1252
(Latin1) and code page 850 (Multilingual Latin1) can store an almost
identical set of characters, so most characters in these two code pages
can be converted from one code page to another without data loss.
If you must communicate with clients using different code pages, the
supported solution is to store your data in Unicode columns. If any of
these options is not feasible, the remaining alternative is to store
the data in binary columns using the binary, varbinary, or image data
types. Binary data, however, can only be sorted and compared in binary
order, making it less flexible than character data.
----
--
Our database is using text fields, not ntext (Unicode) so i guess we
have to look at changing things to unicode.|||Hi
If you are forcing unicode data into char/varchar columns then you could
have problems and it should be changed.
What you may want to find out is why you have inconsistentency in the way
the clients have been configured.
John
"Feldaspar" wrote:

> Taken from
> http://msdn.microsoft.com/library/d...ataencoding.asp
> Disabling the AutoTranslate feature of the SQL Server ODBC driver to
> insert data defined with a different code page from the server is not
> supported. Also, even if AutoTranslate is disabled, it does not prevent
> code page translation for SQL language events. The result is that if
> the client and database code pages do not match, code page translation
> will generally be applied to any non-Unicode character string sent to
> or from the server. (For more information about AutoTranslate, see
> Autotranslation of Character Data and PRB: SQL Server ODBC Driver
> Converts Language Events to Unicode.
> If possible, you should avoid this situation. The best choice for a
> code page-specific server is to communicate only with clients using the
> same code page. The second-best choice is to use another code page that
> has an almost identical character set. For example, code page 1252
> (Latin1) and code page 850 (Multilingual Latin1) can store an almost
> identical set of characters, so most characters in these two code pages
> can be converted from one code page to another without data loss.
> If you must communicate with clients using different code pages, the
> supported solution is to store your data in Unicode columns. If any of
> these options is not feasible, the remaining alternative is to store
> the data in binary columns using the binary, varbinary, or image data
> types. Binary data, however, can only be sorted and compared in binary
> order, making it less flexible than character data.
> ----
--
> Our database is using text fields, not ntext (Unicode) so i guess we
> have to look at changing things to unicode.
>

code pages mess

Hi,
I have inherited a problem with code pages. When the server has a
different code page to the client the unrecognised characters are being
passed across as a question mark - ?.
I believe this is because SQL Server tries to translate it to the code
page it is using but can't find a match.
Is there a way to stop this attempted translation so that it just
passes it over as the character it is?
I'm not really up with different character sets and what is possible.
But the only reliable way to stop it is to make sure both client and
server are using the same code page.
Thank you.Hi
Displaying an unknown character as a question mark is usually a feature of
the client tools and not the server. If you check out the character value in
the database and on the client you will find they are the same.
John
"Feldaspar" wrote:
> Hi,
> I have inherited a problem with code pages. When the server has a
> different code page to the client the unrecognised characters are being
> passed across as a question mark - ?.
> I believe this is because SQL Server tries to translate it to the code
> page it is using but can't find a match.
> Is there a way to stop this attempted translation so that it just
> passes it over as the character it is?
> I'm not really up with different character sets and what is possible.
> But the only reliable way to stop it is to make sure both client and
> server are using the same code page.
> Thank you.
>|||No they are different,
It appears to be a bug with the OLEDB driver, see
http://support.microsoft.com/default.aspx?scid=kb;en-us;234748
John Bell wrote:
> Hi
> Displaying an unknown character as a question mark is usually a feature of
> the client tools and not the server. If you check out the character value in
> the database and on the client you will find they are the same.
> John
> "Feldaspar" wrote:
> > Hi,
> >
> > I have inherited a problem with code pages. When the server has a
> > different code page to the client the unrecognised characters are being
> > passed across as a question mark - ?.
> >
> > I believe this is because SQL Server tries to translate it to the code
> > page it is using but can't find a match.
> >
> > Is there a way to stop this attempted translation so that it just
> > passes it over as the character it is?
> >
> > I'm not really up with different character sets and what is possible.
> > But the only reliable way to stop it is to make sure both client and
> > server are using the same code page.
> >
> > Thank you.
> >
> >|||Hi
The OLEDB driver is a client component! Have you checked whether you are
up-to-date with MDAC using the MDAC checker and load a more recent version if
inconsistent or old http://msdn.microsoft.com/data/ref/mdac/downloads/
John
"Feldaspar" wrote:
> No they are different,
> It appears to be a bug with the OLEDB driver, see
> http://support.microsoft.com/default.aspx?scid=kb;en-us;234748
>
> John Bell wrote:
> > Hi
> >
> > Displaying an unknown character as a question mark is usually a feature of
> > the client tools and not the server. If you check out the character value in
> > the database and on the client you will find they are the same.
> >
> > John
> >
> > "Feldaspar" wrote:
> >
> > > Hi,
> > >
> > > I have inherited a problem with code pages. When the server has a
> > > different code page to the client the unrecognised characters are being
> > > passed across as a question mark - ?.
> > >
> > > I believe this is because SQL Server tries to translate it to the code
> > > page it is using but can't find a match.
> > >
> > > Is there a way to stop this attempted translation so that it just
> > > passes it over as the character it is?
> > >
> > > I'm not really up with different character sets and what is possible.
> > > But the only reliable way to stop it is to make sure both client and
> > > server are using the same code page.
> > >
> > > Thank you.
> > >
> > >
>|||Taken from
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_dataencoding.asp
Disabling the AutoTranslate feature of the SQL Server ODBC driver to
insert data defined with a different code page from the server is not
supported. Also, even if AutoTranslate is disabled, it does not prevent
code page translation for SQL language events. The result is that if
the client and database code pages do not match, code page translation
will generally be applied to any non-Unicode character string sent to
or from the server. (For more information about AutoTranslate, see
Autotranslation of Character Data and PRB: SQL Server ODBC Driver
Converts Language Events to Unicode.
If possible, you should avoid this situation. The best choice for a
code page-specific server is to communicate only with clients using the
same code page. The second-best choice is to use another code page that
has an almost identical character set. For example, code page 1252
(Latin1) and code page 850 (Multilingual Latin1) can store an almost
identical set of characters, so most characters in these two code pages
can be converted from one code page to another without data loss.
If you must communicate with clients using different code pages, the
supported solution is to store your data in Unicode columns. If any of
these options is not feasible, the remaining alternative is to store
the data in binary columns using the binary, varbinary, or image data
types. Binary data, however, can only be sorted and compared in binary
order, making it less flexible than character data.
-----
Our database is using text fields, not ntext (Unicode) so i guess we
have to look at changing things to unicode.|||Hi
If you are forcing unicode data into char/varchar columns then you could
have problems and it should be changed.
What you may want to find out is why you have inconsistentency in the way
the clients have been configured.
John
"Feldaspar" wrote:
> Taken from
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_dataencoding.asp
> Disabling the AutoTranslate feature of the SQL Server ODBC driver to
> insert data defined with a different code page from the server is not
> supported. Also, even if AutoTranslate is disabled, it does not prevent
> code page translation for SQL language events. The result is that if
> the client and database code pages do not match, code page translation
> will generally be applied to any non-Unicode character string sent to
> or from the server. (For more information about AutoTranslate, see
> Autotranslation of Character Data and PRB: SQL Server ODBC Driver
> Converts Language Events to Unicode.
> If possible, you should avoid this situation. The best choice for a
> code page-specific server is to communicate only with clients using the
> same code page. The second-best choice is to use another code page that
> has an almost identical character set. For example, code page 1252
> (Latin1) and code page 850 (Multilingual Latin1) can store an almost
> identical set of characters, so most characters in these two code pages
> can be converted from one code page to another without data loss.
> If you must communicate with clients using different code pages, the
> supported solution is to store your data in Unicode columns. If any of
> these options is not feasible, the remaining alternative is to store
> the data in binary columns using the binary, varbinary, or image data
> types. Binary data, however, can only be sorted and compared in binary
> order, making it less flexible than character data.
> -----
> Our database is using text fields, not ntext (Unicode) so i guess we
> have to look at changing things to unicode.
>

Clustering, virtual sql servers and client network aliases

I have a question regarding the nature of virtual sql servers, specificially what protocol is used to communicate to the server when a request is made by a client.

For example, if a scheduled job is run on the virtual sql server, what determines the protocol used (e.g. TCP/IP, named pipes etc.) by SQL Server agent? Is it the client network alias set up on the virtual server?

I am asking because currently the client aliases on some of our virtual sql servers are using named pipes and I think this is causing a problem with our backups.

Thanks,
Adrian.Sounds like a Windows 2003 cluster running SQL 2000. There is a problem on install that you have to make the named pipe aliases, or the install fails. It sounds like no one cleaned up after themselves after the install. You should be able to remove those aliases (but write down how they were set up, in case you do need them). Clients can talk to virtual SQL Servers on any of the regular protocols.|||Yes, a correct deduction! Thanks for your comments. I am trying to understand the specific role of the client network aliases set up on the virtual sql server. As its a virtual server (essentially a pointer to the active node) does the alias dictate the protocol used?|||When you create the alias, you also identify which of the active protocols (one to a customer) that alias will use.|||Thanks. What I'm trying to get at is the special situation of client network aliases on a virtual sql server. Are they responsible for dictating the protocol used for communication between the virtual server and actual node?|||The aliases are tied to the physical node. Most OS settings are tied only to the physical node, as well, so you have to be careful to keep them both at the same patch level, and with the same settings.

What process are you thinking of that is communicating from the physical node to the virtual server? Most backup packages have support for clusters that do not need to know the name of the physical node. In fact, it is better that they do not know the physical node name, because the physical node name is not actually guaranteed at any point in time.|||Thanks MCrowely. There are some regular SQL backups scheduled but they do not run. Backups can be run manually (through EM or t-sql) and are successful. But when the job is run (manually or automatically as scheduled) it fails.

Diagnosing the problem I have checked the usual things: permissions (e.g. users associated with the context have permission to write a backup file), disk space. Logically, the only factor that might explain it is client network aliases on the virtual server. For failing backups run from the job, aliases use named pipes. Other successful backups are using tcp/ip.

I'd like to fully understand the significance of the aliases on virtual server before I change them and investigate its affect.

Can you shed any additional light on this please?

Thanks.|||Not sure what else to say. Details of how each network protocol works is usually pretty transparent. Perhaps if you looked at step 5 in the workaround?

http://support.microsoft.com/kb/815431|||Thanks anyway MCrowley.

I'm going to go ahead with the change to remove the named pipe aliases and replace them with tcp/ip (making a note of original settings and performing it outside business hours!)

Clustered Indexes

I have a client who has several tables all of which have no Clustered Index
created on them. For the sake of discussion, let's look at one of the larger
tables 1 million plus rows. The only index is a priamry key constraint
creating a unique non clustered index with a fillfactor of 90. The key fields
are
memberid int 4,
serialnum char(17),
stocknum char(20)
This table every night runs through about 3000 update / inserts. None of the
updates include the key fields. Due to processing rules DTS is not used and
all operations are logged. One program running on the server (single
threaded) does all the updates.
I was under the impression that this index should be a clustered index.
I have read a couple of posts which would suggest I am wrong.
Does anyone have any input? Would it be correct to leave this table with no
clustered index. Note when they work on data it is by memberid as thing are
now there is no physical sort to the data files. If the clstered index was
just put on memberid SQL Server would append a row id so it's unique. How
much overhead is invloved in this?
I have never seen this approach before. Is leaving the table as a heap and
having only a non clustered index which would store a row locator as opposed
to the clustered key more efficient?
Thanks,
Jon A
Jon A wrote:
> I have a client who has several tables all of which have no Clustered
> Index created on them. For the sake of discussion, let's look at one
> of the larger tables 1 million plus rows. The only index is a priamry
<SNIP>
Depending entirely on how the table is used and what queries are running
against it. Using a natural key, like the one used in the table, as
clustered could cause page splitting if the table were insert heavy. It
could also help if many queries are run against the table that involve
sets of member ids.
I think your bigger problem is this:
"This table every night runs through about 3000 update / inserts. None
of the
updates include the key fields."
I would add that your key is very large (41 characters). If you make it
clustered, you'll add a lot of overhead to all non-clustered indexes,
should you ever add them.
I would leave the PK index as is right now and look into adding an
additional index to help the nightly updates and any other queries that
are run against the table that have no index to help them.
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" wrote:

> I would leave the PK index as is right now and look into adding an
> additional index to help the nightly updates and any other queries that
> are run against the table that have no index to help them.
>
I tend to be in agreement with leaving this primary key non clustered, but I
am wondering since all work with the data is done on member id would that be
a good choice for a clustered index and let SQL add the row id so it is
unique or just leave the table without a clustered index? Since the update
program pulls data by member is it more efficient to have it sorted that way.
And just use a small fill factor along with periodically updating stats and
performing index maintainance.
|||If MemberID is the column used most often to fetch and update the rows then
it makes a lot of sense to cluster on it. You can alleviate a bookmark
lookup with each call. 3000 updates a night is trivial and just make a
reasonable fill factor to account for some inserts and minor updates. Even
forgetting about all that a clustered index can actually increase
performance on inserts under the right conditions. And without a CI there
is no way to control fragmentation.
Andrew J. Kelly SQL MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:54770860-AA65-45A8-8C7D-F6169FD0AAFF@.microsoft.com...
> "David Gugick" wrote:
> I tend to be in agreement with leaving this primary key non clustered, but
> I
> am wondering since all work with the data is done on member id would that
> be
> a good choice for a clustered index and let SQL add the row id so it is
> unique or just leave the table without a clustered index? Since the update
> program pulls data by member is it more efficient to have it sorted that
> way.
> And just use a small fill factor along with periodically updating stats
> and
> performing index maintainance.
|||"Andrew J. Kelly" wrote:

> If MemberID is the column used most often to fetch and update the rows then
> it makes a lot of sense to cluster on it. You can alleviate a bookmark
> lookup with each call. 3000 updates a night is trivial and just make a
> reasonable fill factor to account for some inserts and minor updates. Even
> forgetting about all that a clustered index can actually increase
> performance on inserts under the right conditions. And without a CI there
> is no way to control fragmentation.
>
As I am looking through the code most of the updates and searches are done by
memberid int 4,
serialnum char(17),
stocknum char(20)
Should this then be clustered?
|||Member Id is the primary key...
Are the inserts (added members) monotonically increasing in value
(Incrementing) ?
If so, then inserts will probably not cause Page Splits. However, if this is
not the case Inserts will cause splitting.
Either way, updates, deletes will likely cause splits to occur.
eventually the table will become fragmented and you will not be able to
defrag it because it is a Heap (No clustered Index)
I highly recommend that you place "A" clustered index on the table (and all
tables for that matter).
You do NOT have to cluster on the Pkey. Cluster on the column that you most
often sort on or perform range searches on.
I just left a company that had 100% heaps in prod. Over time, our SQL
Database absolutely TANKED.
it was VERY difficult to create clusters AFTER The system went live. It
required downtime and a HUGE Level of effort to accomplish the task (Not to
mention a HUGE amount of political strife as our other DBA swore I was
smoking the crack pipe in my recommendation)
Since we finally put clusters and Index Maintenance plans in place, the prod
system is smoking.
cheers
Greg Jackson
Portland, Oregon
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:59FB13D5-1B57-4E73-9545-30CC1CAA8C9F@.microsoft.com...
> "Andrew J. Kelly" wrote:
> As I am looking through the code most of the updates and searches are done
> by
> memberid int 4,
> serialnum char(17),
> stocknum char(20)
> Should this then be clustered?
|||Do you mean all three columns at once or each individually? If it is all
three then the other two are mostly useless if you were to include them in
the clustered index definition. That is due to the fact that MemberID is the
PK and there will only be 1 row that matches that ID. So I would make the
PK the CI and be done with it. If you also search on the other two
individually it would depend on how selective they are. How many rows are
there with each value?
Andrew J. Kelly SQL MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:59FB13D5-1B57-4E73-9545-30CC1CAA8C9F@.microsoft.com...
> "Andrew J. Kelly" wrote:
> As I am looking through the code most of the updates and searches are done
> by
> memberid int 4,
> serialnum char(17),
> stocknum char(20)
> Should this then be clustered?
|||Greg,
[vbcol=seagreen]
Actually Deletes will not cause a page split. Inserts and updates may cause
page splits but a proper fill factor and routine reindexing can alleviate
most of that.
Andrew J. Kelly SQL MVP
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:O25DkpvFFHA.3504@.TK2MSFTNGP12.phx.gbl...
> Member Id is the primary key...
> Are the inserts (added members) monotonically increasing in value
> (Incrementing) ?
> If so, then inserts will probably not cause Page Splits. However, if this
> is not the case Inserts will cause splitting.
> Either way, updates, deletes will likely cause splits to occur.
> eventually the table will become fragmented and you will not be able to
> defrag it because it is a Heap (No clustered Index)
> I highly recommend that you place "A" clustered index on the table (and
> all tables for that matter).
> You do NOT have to cluster on the Pkey. Cluster on the column that you
> most often sort on or perform range searches on.
> I just left a company that had 100% heaps in prod. Over time, our SQL
> Database absolutely TANKED.
> it was VERY difficult to create clusters AFTER The system went live. It
> required downtime and a HUGE Level of effort to accomplish the task (Not
> to mention a HUGE amount of political strife as our other DBA swore I was
> smoking the crack pipe in my recommendation)
> Since we finally put clusters and Index Maintenance plans in place, the
> prod system is smoking.
>
> cheers
> Greg Jackson
> Portland, Oregon
> "Jon A" <JonA@.discussions.microsoft.com> wrote in message
> news:59FB13D5-1B57-4E73-9545-30CC1CAA8C9F@.microsoft.com...
>
|||memberid int 4,
serialnum char(17),
stocknum char(20)
This is the primary key and defines uniqueness in the table. A member can
have multiple items and the same item may have multiple stock numbers.(please
note I didn't design this I am just trying to solve there issues of
performance. They do not want to ovewrhaul the design.) The primary key is
now non clustered. All lookups for updates or insert are done by searching on
these fields and ordering the results on these fields. The only thing is they
process a member at a time. The performance problem they have is as the
tables have grown, after one night of uploads they need to stop SQL server
and restart. Or the next nights run goes from 4 hours to 12 hours. The memory
in SQL grows until there is little left for the Web Server. I believe the
memory problem is due to the fragmentation of the tables due to being heap
tables. The machine is 2 processor P4 with 2 gig ram and a SCSI 3 disk raid 5
with 32 meg ram. The batch program runs on the server and is single
connection,single threaded, and nothing else is running(no lock problems).
Would it help the fragmentation to Cluster on memberid because as the batch
runs it goes a member at a time?
|||Jon A wrote:
> memberid int 4,
> serialnum char(17),
> stocknum char(20)
> This is the primary key and defines uniqueness in the table. A member
> can have multiple items and the same item may have multiple stock
> numbers.(please note I didn't design this I am just trying to solve
> there issues of performance. They do not want to ovewrhaul the
> design.) The primary key is now non clustered. All lookups for
> updates or insert are done by searching on these fields and ordering
> the results on these fields. The only thing is they process a member
> at a time. The performance problem they have is as the tables have
> grown, after one night of uploads they need to stop SQL server and
> restart. Or the next nights run goes from 4 hours to 12 hours. The
> memory in SQL grows until there is little left for the Web Server. I
> believe the memory problem is due to the fragmentation of the tables
> due to being heap tables. The machine is 2 processor P4 with 2 gig
> ram and a SCSI 3 disk raid 5 with 32 meg ram. The batch program runs
> on the server and is single connection,single threaded, and nothing
> else is running(no lock problems). Would it help the fragmentation to
> Cluster on memberid because as the batch runs it goes a member at a
> time?
First I would get rid of any sorting operations against this table.
ORDER BY clauses are rarely required by production applications and add
unnecessary overhead.
If you are processing a member id at a time (how are you doing this?),
then it does make some sense to use a clustered index. If you use a
set-based operation on the ID, having a clustered index will help a lot.
If you are accessing IDs one at a time, then it may not help much over a
non-clustered index as other processes may cause the disk heads to move
somewhere else between ID queries. At the very least, you'll get rid of
the bookmark lookup operation which can be responsible for 50% of a
query's overhead.
But the clustered index design does pose some problems. On one hand,
clustering the entire PK should help your queries and enforce the PK
constraint. On the other hand, large clustered keys cause non-clustered
indexes to grow (since the clustered key is part of the non-clustered
index). But since you have no non-clustered indexes right now, I would
cluster on the PK.
The memory issue is a configuration problem at your end. SQL Server uses
memory as it reads data, and generally does not release memory back into
the system unless specifically asked to do so by the OS. You are running
a web server on the same box. When running multiple applications on the
same box, it's imperative to set a maximum memory limit for SQL Server,
or you'll likely run into the situation you already see. That is, SQL
Server uses so much memory that other services are left memory-starved.
One reason SQL Server can chew up memory fast is when it runs many
unoptimized queries. As the data is read, it is loaded into memory.
Memory is not released, but the pages in memory are likely being swapped
as others are read. So check for queries that require tuning (like the
nightly upoptimized updates).
But the best way to avoid SQL Server using too much memory is to set a
memory limit.
David Gugick
Imceda Software
www.imceda.com

Clustered Indexes

I have a client who has several tables all of which have no Clustered Index
created on them. For the sake of discussion, let's look at one of the larger
tables 1 million plus rows. The only index is a priamry key constraint
creating a unique non clustered index with a fillfactor of 90. The key field
s
are
memberid int 4,
serialnum char(17),
stocknum char(20)
This table every night runs through about 3000 update / inserts. None of the
updates include the key fields. Due to processing rules DTS is not used and
all operations are logged. One program running on the server (single
threaded) does all the updates.
I was under the impression that this index should be a clustered index.
I have read a couple of posts which would suggest I am wrong.
Does anyone have any input? Would it be correct to leave this table with no
clustered index. Note when they work on data it is by memberid as thing are
now there is no physical sort to the data files. If the clstered index was
just put on memberid SQL Server would append a row id so it's unique. How
much overhead is invloved in this?
I have never seen this approach before. Is leaving the table as a heap and
having only a non clustered index which would store a row locator as opposed
to the clustered key more efficient?
--
Thanks,
Jon AJon A wrote:
> I have a client who has several tables all of which have no Clustered
> Index created on them. For the sake of discussion, let's look at one
> of the larger tables 1 million plus rows. The only index is a priamry
<SNIP>
Depending entirely on how the table is used and what queries are running
against it. Using a natural key, like the one used in the table, as
clustered could cause page splitting if the table were insert heavy. It
could also help if many queries are run against the table that involve
sets of member ids.
I think your bigger problem is this:
"This table every night runs through about 3000 update / inserts. None
of the
updates include the key fields."
I would add that your key is very large (41 characters). If you make it
clustered, you'll add a lot of overhead to all non-clustered indexes,
should you ever add them.
I would leave the PK index as is right now and look into adding an
additional index to help the nightly updates and any other queries that
are run against the table that have no index to help them.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" wrote:

> I would leave the PK index as is right now and look into adding an
> additional index to help the nightly updates and any other queries that
> are run against the table that have no index to help them.
>
I tend to be in agreement with leaving this primary key non clustered, but I
am wondering since all work with the data is done on member id would that be
a good choice for a clustered index and let SQL add the row id so it is
unique or just leave the table without a clustered index? Since the update
program pulls data by member is it more efficient to have it sorted that way
.
And just use a small fill factor along with periodically updating stats and
performing index maintainance.|||If MemberID is the column used most often to fetch and update the rows then
it makes a lot of sense to cluster on it. You can alleviate a bookmark
lookup with each call. 3000 updates a night is trivial and just make a
reasonable fill factor to account for some inserts and minor updates. Even
forgetting about all that a clustered index can actually increase
performance on inserts under the right conditions. And without a CI there
is no way to control fragmentation.
Andrew J. Kelly SQL MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:54770860-AA65-45A8-8C7D-F6169FD0AAFF@.microsoft.com...
> "David Gugick" wrote:
>
> I tend to be in agreement with leaving this primary key non clustered, but
> I
> am wondering since all work with the data is done on member id would that
> be
> a good choice for a clustered index and let SQL add the row id so it is
> unique or just leave the table without a clustered index? Since the update
> program pulls data by member is it more efficient to have it sorted that
> way.
> And just use a small fill factor along with periodically updating stats
> and
> performing index maintainance.|||"Andrew J. Kelly" wrote:

> If MemberID is the column used most often to fetch and update the rows the
n
> it makes a lot of sense to cluster on it. You can alleviate a bookmark
> lookup with each call. 3000 updates a night is trivial and just make a
> reasonable fill factor to account for some inserts and minor updates. Eve
n
> forgetting about all that a clustered index can actually increase
> performance on inserts under the right conditions. And without a CI there
> is no way to control fragmentation.
>
As I am looking through the code most of the updates and searches are done b
y
memberid int 4,
serialnum char(17),
stocknum char(20)
Should this then be clustered?|||Member Id is the primary key...
Are the inserts (added members) monotonically increasing in value
(Incrementing) ?
If so, then inserts will probably not cause Page Splits. However, if this is
not the case Inserts will cause splitting.
Either way, updates, deletes will likely cause splits to occur.
eventually the table will become fragmented and you will not be able to
defrag it because it is a Heap (No clustered Index)
I highly recommend that you place "A" clustered index on the table (and all
tables for that matter).
You do NOT have to cluster on the Pkey. Cluster on the column that you most
often sort on or perform range searches on.
I just left a company that had 100% heaps in prod. Over time, our SQL
Database absolutely TANKED.
it was VERY difficult to create clusters AFTER The system went live. It
required downtime and a HUGE Level of effort to accomplish the task (Not to
mention a HUGE amount of political strife as our other DBA swore I was
smoking the crack pipe in my recommendation)
Since we finally put clusters and Index Maintenance plans in place, the prod
system is smoking.
cheers
Greg Jackson
Portland, Oregon
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:59FB13D5-1B57-4E73-9545-30CC1CAA8C9F@.microsoft.com...
> "Andrew J. Kelly" wrote:
>
> As I am looking through the code most of the updates and searches are done
> by
> memberid int 4,
> serialnum char(17),
> stocknum char(20)
> Should this then be clustered?|||Do you mean all three columns at once or each individually? If it is all
three then the other two are mostly useless if you were to include them in
the clustered index definition. That is due to the fact that MemberID is the
PK and there will only be 1 row that matches that ID. So I would make the
PK the CI and be done with it. If you also search on the other two
individually it would depend on how selective they are. How many rows are
there with each value?
Andrew J. Kelly SQL MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:59FB13D5-1B57-4E73-9545-30CC1CAA8C9F@.microsoft.com...
> "Andrew J. Kelly" wrote:
>
> As I am looking through the code most of the updates and searches are done
> by
> memberid int 4,
> serialnum char(17),
> stocknum char(20)
> Should this then be clustered?|||Greg,

Actually Deletes will not cause a page split. Inserts and updates may cause
page splits but a proper fill factor and routine reindexing can alleviate
most of that.
Andrew J. Kelly SQL MVP
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:O25DkpvFFHA.3504@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Member Id is the primary key...
> Are the inserts (added members) monotonically increasing in value
> (Incrementing) ?
> If so, then inserts will probably not cause Page Splits. However, if this
> is not the case Inserts will cause splitting.
> Either way, updates, deletes will likely cause splits to occur.
> eventually the table will become fragmented and you will not be able to
> defrag it because it is a Heap (No clustered Index)
> I highly recommend that you place "A" clustered index on the table (and
> all tables for that matter).
> You do NOT have to cluster on the Pkey. Cluster on the column that you
> most often sort on or perform range searches on.
> I just left a company that had 100% heaps in prod. Over time, our SQL
> Database absolutely TANKED.
> it was VERY difficult to create clusters AFTER The system went live. It
> required downtime and a HUGE Level of effort to accomplish the task (Not
> to mention a HUGE amount of political strife as our other DBA swore I was
> smoking the crack pipe in my recommendation)
> Since we finally put clusters and Index Maintenance plans in place, the
> prod system is smoking.
>
> cheers
> Greg Jackson
> Portland, Oregon
> "Jon A" <JonA@.discussions.microsoft.com> wrote in message
> news:59FB13D5-1B57-4E73-9545-30CC1CAA8C9F@.microsoft.com...
>|||memberid int 4,
serialnum char(17),
stocknum char(20)
This is the primary key and defines uniqueness in the table. A member can
have multiple items and the same item may have multiple stock numbers.(pleas
e
note I didn't design this I am just trying to solve there issues of
performance. They do not want to ovewrhaul the design.) The primary key is
now non clustered. All lookups for updates or insert are done by searching o
n
these fields and ordering the results on these fields. The only thing is the
y
process a member at a time. The performance problem they have is as the
tables have grown, after one night of uploads they need to stop SQL server
and restart. Or the next nights run goes from 4 hours to 12 hours. The memor
y
in SQL grows until there is little left for the Web Server. I believe the
memory problem is due to the fragmentation of the tables due to being heap
tables. The machine is 2 processor P4 with 2 gig ram and a SCSI 3 disk raid
5
with 32 meg ram. The batch program runs on the server and is single
connection,single threaded, and nothing else is running(no lock problems).
Would it help the fragmentation to Cluster on memberid because as the batch
runs it goes a member at a time?|||Jon A wrote:
> memberid int 4,
> serialnum char(17),
> stocknum char(20)
> This is the primary key and defines uniqueness in the table. A member
> can have multiple items and the same item may have multiple stock
> numbers.(please note I didn't design this I am just trying to solve
> there issues of performance. They do not want to ovewrhaul the
> design.) The primary key is now non clustered. All lookups for
> updates or insert are done by searching on these fields and ordering
> the results on these fields. The only thing is they process a member
> at a time. The performance problem they have is as the tables have
> grown, after one night of uploads they need to stop SQL server and
> restart. Or the next nights run goes from 4 hours to 12 hours. The
> memory in SQL grows until there is little left for the Web Server. I
> believe the memory problem is due to the fragmentation of the tables
> due to being heap tables. The machine is 2 processor P4 with 2 gig
> ram and a SCSI 3 disk raid 5 with 32 meg ram. The batch program runs
> on the server and is single connection,single threaded, and nothing
> else is running(no lock problems). Would it help the fragmentation to
> Cluster on memberid because as the batch runs it goes a member at a
> time?
First I would get rid of any sorting operations against this table.
ORDER BY clauses are rarely required by production applications and add
unnecessary overhead.
If you are processing a member id at a time (how are you doing this?),
then it does make some sense to use a clustered index. If you use a
set-based operation on the ID, having a clustered index will help a lot.
If you are accessing IDs one at a time, then it may not help much over a
non-clustered index as other processes may cause the disk heads to move
somewhere else between ID queries. At the very least, you'll get rid of
the bookmark lookup operation which can be responsible for 50% of a
query's overhead.
But the clustered index design does pose some problems. On one hand,
clustering the entire PK should help your queries and enforce the PK
constraint. On the other hand, large clustered keys cause non-clustered
indexes to grow (since the clustered key is part of the non-clustered
index). But since you have no non-clustered indexes right now, I would
cluster on the PK.
The memory issue is a configuration problem at your end. SQL Server uses
memory as it reads data, and generally does not release memory back into
the system unless specifically asked to do so by the OS. You are running
a web server on the same box. When running multiple applications on the
same box, it's imperative to set a maximum memory limit for SQL Server,
or you'll likely run into the situation you already see. That is, SQL
Server uses so much memory that other services are left memory-starved.
One reason SQL Server can chew up memory fast is when it runs many
unoptimized queries. As the data is read, it is loaded into memory.
Memory is not released, but the pages in memory are likely being swapped
as others are read. So check for queries that require tuning (like the
nightly upoptimized updates).
But the best way to avoid SQL Server using too much memory is to set a
memory limit.
David Gugick
Imceda Software
www.imceda.com

Clustered Indexes

I have a client who has several tables all of which have no Clustered Index
created on them. For the sake of discussion, let's look at one of the larger
tables 1 million plus rows. The only index is a priamry key constraint
creating a unique non clustered index with a fillfactor of 90. The key fields
are
memberid int 4,
serialnum char(17),
stocknum char(20)
This table every night runs through about 3000 update / inserts. None of the
updates include the key fields. Due to processing rules DTS is not used and
all operations are logged. One program running on the server (single
threaded) does all the updates.
I was under the impression that this index should be a clustered index.
I have read a couple of posts which would suggest I am wrong.
Does anyone have any input? Would it be correct to leave this table with no
clustered index. Note when they work on data it is by memberid as thing are
now there is no physical sort to the data files. If the clstered index was
just put on memberid SQL Server would append a row id so it's unique. How
much overhead is invloved in this?
I have never seen this approach before. Is leaving the table as a heap and
having only a non clustered index which would store a row locator as opposed
to the clustered key more efficient?
--
Thanks,
Jon AJon A wrote:
> I have a client who has several tables all of which have no Clustered
> Index created on them. For the sake of discussion, let's look at one
> of the larger tables 1 million plus rows. The only index is a priamry
<SNIP>
Depending entirely on how the table is used and what queries are running
against it. Using a natural key, like the one used in the table, as
clustered could cause page splitting if the table were insert heavy. It
could also help if many queries are run against the table that involve
sets of member ids.
I think your bigger problem is this:
"This table every night runs through about 3000 update / inserts. None
of the
updates include the key fields."
I would add that your key is very large (41 characters). If you make it
clustered, you'll add a lot of overhead to all non-clustered indexes,
should you ever add them.
I would leave the PK index as is right now and look into adding an
additional index to help the nightly updates and any other queries that
are run against the table that have no index to help them.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" wrote:
> I would leave the PK index as is right now and look into adding an
> additional index to help the nightly updates and any other queries that
> are run against the table that have no index to help them.
>
I tend to be in agreement with leaving this primary key non clustered, but I
am wondering since all work with the data is done on member id would that be
a good choice for a clustered index and let SQL add the row id so it is
unique or just leave the table without a clustered index? Since the update
program pulls data by member is it more efficient to have it sorted that way.
And just use a small fill factor along with periodically updating stats and
performing index maintainance.|||If MemberID is the column used most often to fetch and update the rows then
it makes a lot of sense to cluster on it. You can alleviate a bookmark
lookup with each call. 3000 updates a night is trivial and just make a
reasonable fill factor to account for some inserts and minor updates. Even
forgetting about all that a clustered index can actually increase
performance on inserts under the right conditions. And without a CI there
is no way to control fragmentation.
--
Andrew J. Kelly SQL MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:54770860-AA65-45A8-8C7D-F6169FD0AAFF@.microsoft.com...
> "David Gugick" wrote:
>> I would leave the PK index as is right now and look into adding an
>> additional index to help the nightly updates and any other queries that
>> are run against the table that have no index to help them.
> I tend to be in agreement with leaving this primary key non clustered, but
> I
> am wondering since all work with the data is done on member id would that
> be
> a good choice for a clustered index and let SQL add the row id so it is
> unique or just leave the table without a clustered index? Since the update
> program pulls data by member is it more efficient to have it sorted that
> way.
> And just use a small fill factor along with periodically updating stats
> and
> performing index maintainance.|||"Andrew J. Kelly" wrote:
> If MemberID is the column used most often to fetch and update the rows then
> it makes a lot of sense to cluster on it. You can alleviate a bookmark
> lookup with each call. 3000 updates a night is trivial and just make a
> reasonable fill factor to account for some inserts and minor updates. Even
> forgetting about all that a clustered index can actually increase
> performance on inserts under the right conditions. And without a CI there
> is no way to control fragmentation.
>
As I am looking through the code most of the updates and searches are done by
memberid int 4,
serialnum char(17),
stocknum char(20)
Should this then be clustered?|||Member Id is the primary key...
Are the inserts (added members) monotonically increasing in value
(Incrementing) ?
If so, then inserts will probably not cause Page Splits. However, if this is
not the case Inserts will cause splitting.
Either way, updates, deletes will likely cause splits to occur.
eventually the table will become fragmented and you will not be able to
defrag it because it is a Heap (No clustered Index)
I highly recommend that you place "A" clustered index on the table (and all
tables for that matter).
You do NOT have to cluster on the Pkey. Cluster on the column that you most
often sort on or perform range searches on.
I just left a company that had 100% heaps in prod. Over time, our SQL
Database absolutely TANKED.
it was VERY difficult to create clusters AFTER The system went live. It
required downtime and a HUGE Level of effort to accomplish the task (Not to
mention a HUGE amount of political strife as our other DBA swore I was
smoking the crack pipe in my recommendation)
Since we finally put clusters and Index Maintenance plans in place, the prod
system is smoking.
cheers
Greg Jackson
Portland, Oregon
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:59FB13D5-1B57-4E73-9545-30CC1CAA8C9F@.microsoft.com...
> "Andrew J. Kelly" wrote:
>> If MemberID is the column used most often to fetch and update the rows
>> then
>> it makes a lot of sense to cluster on it. You can alleviate a bookmark
>> lookup with each call. 3000 updates a night is trivial and just make a
>> reasonable fill factor to account for some inserts and minor updates.
>> Even
>> forgetting about all that a clustered index can actually increase
>> performance on inserts under the right conditions. And without a CI
>> there
>> is no way to control fragmentation.
> As I am looking through the code most of the updates and searches are done
> by
> memberid int 4,
> serialnum char(17),
> stocknum char(20)
> Should this then be clustered?|||Do you mean all three columns at once or each individually? If it is all
three then the other two are mostly useless if you were to include them in
the clustered index definition. That is due to the fact that MemberID is the
PK and there will only be 1 row that matches that ID. So I would make the
PK the CI and be done with it. If you also search on the other two
individually it would depend on how selective they are. How many rows are
there with each value?
--
Andrew J. Kelly SQL MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:59FB13D5-1B57-4E73-9545-30CC1CAA8C9F@.microsoft.com...
> "Andrew J. Kelly" wrote:
>> If MemberID is the column used most often to fetch and update the rows
>> then
>> it makes a lot of sense to cluster on it. You can alleviate a bookmark
>> lookup with each call. 3000 updates a night is trivial and just make a
>> reasonable fill factor to account for some inserts and minor updates.
>> Even
>> forgetting about all that a clustered index can actually increase
>> performance on inserts under the right conditions. And without a CI
>> there
>> is no way to control fragmentation.
> As I am looking through the code most of the updates and searches are done
> by
> memberid int 4,
> serialnum char(17),
> stocknum char(20)
> Should this then be clustered?|||Greg,
>>Either way, updates, deletes will likely cause splits to occur.
Actually Deletes will not cause a page split. Inserts and updates may cause
page splits but a proper fill factor and routine reindexing can alleviate
most of that.
--
Andrew J. Kelly SQL MVP
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:O25DkpvFFHA.3504@.TK2MSFTNGP12.phx.gbl...
> Member Id is the primary key...
> Are the inserts (added members) monotonically increasing in value
> (Incrementing) ?
> If so, then inserts will probably not cause Page Splits. However, if this
> is not the case Inserts will cause splitting.
> Either way, updates, deletes will likely cause splits to occur.
> eventually the table will become fragmented and you will not be able to
> defrag it because it is a Heap (No clustered Index)
> I highly recommend that you place "A" clustered index on the table (and
> all tables for that matter).
> You do NOT have to cluster on the Pkey. Cluster on the column that you
> most often sort on or perform range searches on.
> I just left a company that had 100% heaps in prod. Over time, our SQL
> Database absolutely TANKED.
> it was VERY difficult to create clusters AFTER The system went live. It
> required downtime and a HUGE Level of effort to accomplish the task (Not
> to mention a HUGE amount of political strife as our other DBA swore I was
> smoking the crack pipe in my recommendation)
> Since we finally put clusters and Index Maintenance plans in place, the
> prod system is smoking.
>
> cheers
> Greg Jackson
> Portland, Oregon
> "Jon A" <JonA@.discussions.microsoft.com> wrote in message
> news:59FB13D5-1B57-4E73-9545-30CC1CAA8C9F@.microsoft.com...
>> "Andrew J. Kelly" wrote:
>> If MemberID is the column used most often to fetch and update the rows
>> then
>> it makes a lot of sense to cluster on it. You can alleviate a bookmark
>> lookup with each call. 3000 updates a night is trivial and just make a
>> reasonable fill factor to account for some inserts and minor updates.
>> Even
>> forgetting about all that a clustered index can actually increase
>> performance on inserts under the right conditions. And without a CI
>> there
>> is no way to control fragmentation.
>> As I am looking through the code most of the updates and searches are
>> done by
>> memberid int 4,
>> serialnum char(17),
>> stocknum char(20)
>> Should this then be clustered?
>|||memberid int 4,
serialnum char(17),
stocknum char(20)
This is the primary key and defines uniqueness in the table. A member can
have multiple items and the same item may have multiple stock numbers.(please
note I didn't design this I am just trying to solve there issues of
performance. They do not want to ovewrhaul the design.) The primary key is
now non clustered. All lookups for updates or insert are done by searching on
these fields and ordering the results on these fields. The only thing is they
process a member at a time. The performance problem they have is as the
tables have grown, after one night of uploads they need to stop SQL server
and restart. Or the next nights run goes from 4 hours to 12 hours. The memory
in SQL grows until there is little left for the Web Server. I believe the
memory problem is due to the fragmentation of the tables due to being heap
tables. The machine is 2 processor P4 with 2 gig ram and a SCSI 3 disk raid 5
with 32 meg ram. The batch program runs on the server and is single
connection,single threaded, and nothing else is running(no lock problems).
Would it help the fragmentation to Cluster on memberid because as the batch
runs it goes a member at a time?|||Jon A wrote:
> memberid int 4,
> serialnum char(17),
> stocknum char(20)
> This is the primary key and defines uniqueness in the table. A member
> can have multiple items and the same item may have multiple stock
> numbers.(please note I didn't design this I am just trying to solve
> there issues of performance. They do not want to ovewrhaul the
> design.) The primary key is now non clustered. All lookups for
> updates or insert are done by searching on these fields and ordering
> the results on these fields. The only thing is they process a member
> at a time. The performance problem they have is as the tables have
> grown, after one night of uploads they need to stop SQL server and
> restart. Or the next nights run goes from 4 hours to 12 hours. The
> memory in SQL grows until there is little left for the Web Server. I
> believe the memory problem is due to the fragmentation of the tables
> due to being heap tables. The machine is 2 processor P4 with 2 gig
> ram and a SCSI 3 disk raid 5 with 32 meg ram. The batch program runs
> on the server and is single connection,single threaded, and nothing
> else is running(no lock problems). Would it help the fragmentation to
> Cluster on memberid because as the batch runs it goes a member at a
> time?
First I would get rid of any sorting operations against this table.
ORDER BY clauses are rarely required by production applications and add
unnecessary overhead.
If you are processing a member id at a time (how are you doing this?),
then it does make some sense to use a clustered index. If you use a
set-based operation on the ID, having a clustered index will help a lot.
If you are accessing IDs one at a time, then it may not help much over a
non-clustered index as other processes may cause the disk heads to move
somewhere else between ID queries. At the very least, you'll get rid of
the bookmark lookup operation which can be responsible for 50% of a
query's overhead.
But the clustered index design does pose some problems. On one hand,
clustering the entire PK should help your queries and enforce the PK
constraint. On the other hand, large clustered keys cause non-clustered
indexes to grow (since the clustered key is part of the non-clustered
index). But since you have no non-clustered indexes right now, I would
cluster on the PK.
The memory issue is a configuration problem at your end. SQL Server uses
memory as it reads data, and generally does not release memory back into
the system unless specifically asked to do so by the OS. You are running
a web server on the same box. When running multiple applications on the
same box, it's imperative to set a maximum memory limit for SQL Server,
or you'll likely run into the situation you already see. That is, SQL
Server uses so much memory that other services are left memory-starved.
One reason SQL Server can chew up memory fast is when it runs many
unoptimized queries. As the data is read, it is loaded into memory.
Memory is not released, but the pages in memory are likely being swapped
as others are read. So check for queries that require tuning (like the
nightly upoptimized updates).
But the best way to avoid SQL Server using too much memory is to set a
memory limit.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" wrote:
Server uses so much memory that other services are left memory-starved.
> One reason SQL Server can chew up memory fast is when it runs many
> unoptimized queries. As the data is read, it is loaded into memory.
> Memory is not released, but the pages in memory are likely being swapped
> as others are read. So check for queries that require tuning (like the
> nightly upoptimized updates).
>
I have one issue with the memory, nothing else on the server slows down.
Just SQL Server. They tried setting the memory option and it didn't help the
situation. I am truly confused as to why shutting down SQL Server after one
day of running would make such a difference. I can run the same updates to
the same database (using saved set and a backup) and if the server has been
running for one other upload it crawls.|||Jon A wrote:
> "David Gugick" wrote:
> Server uses so much memory that other services are left
> memory-starved.
>> One reason SQL Server can chew up memory fast is when it runs many
>> unoptimized queries. As the data is read, it is loaded into memory.
>> Memory is not released, but the pages in memory are likely being
>> swapped as others are read. So check for queries that require tuning
>> (like the nightly upoptimized updates).
> I have one issue with the memory, nothing else on the server slows
> down. Just SQL Server. They tried setting the memory option and it
> didn't help the situation. I am truly confused as to why shutting
> down SQL Server after one day of running would make such a
> difference. I can run the same updates to the same database (using
> saved set and a backup) and if the server has been running for one
> other upload it crawls.
Post the execution plans for these uploads (updates?). I thought you
mentioned in your original post that they were all on columns that were
not indexed. That's going to require table scan ops on each update, and
potentially lock the entire table... which is bad.
--
David Gugick
Imceda Software
www.imceda.com|||agreed with deletes, I misquoted.
gaj|||In addition to what David said, I suspect you are using adhoc queries and
not stored procedures. You are probably saturating your memory with useless
cached plans. You should try to make the plans reusable and you will use a
lot less memory for sql server.
Andrew J. Kelly SQL MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:C37D2754-D832-43BE-9A9B-DAA7F1515304@.microsoft.com...
>
> "David Gugick" wrote:
> Server uses so much memory that other services are left memory-starved.
>> One reason SQL Server can chew up memory fast is when it runs many
>> unoptimized queries. As the data is read, it is loaded into memory.
>> Memory is not released, but the pages in memory are likely being swapped
>> as others are read. So check for queries that require tuning (like the
>> nightly upoptimized updates).
> I have one issue with the memory, nothing else on the server slows down.
> Just SQL Server. They tried setting the memory option and it didn't help
> the
> situation. I am truly confused as to why shutting down SQL Server after
> one
> day of running would make such a difference. I can run the same updates to
> the same database (using saved set and a backup) and if the server has
> been
> running for one other upload it crawls.|||There queries are done through Stored procedures but some of them build the
SQL on the fly in the Stored proc and 'execute(@.SQLString)'. This will be
executed like an ad-hoc query? I agree with all these sugestions, I just
can't get by the fact that there is no locking issue because there is only
one connection and one recordset used at a time during the batch. If Sql
Server takes up all that memory, why should it get slower? Is it that because
of this poor design and implementation (no indexes, fragmented heap
tables,poorly coded stored procedures) SQL is using it up in the cache? What
would be the best counters to tell what SQL has free of the memory it grabs?|||If you use up most of thememory for the procedure cache you will take memory
away from other parts, mainly the buffer cache. That means that for one
thing you get lot more disk I/O. But if you are starving the OS as well it
will have to spend a lot of resources dynamically swapping memory back and
forth between the OS and SQL Server. It gets worse if you have other apps
or processes on the same machine such as IIS, web services, vb apps etc.
How much memory do you have and what settings do you have for Sql Servers
memory?
--
Andrew J. Kelly SQL MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:EAE5BB33-95FF-4D3C-801A-91A537C09F32@.microsoft.com...
> There queries are done through Stored procedures but some of them build
> the
> SQL on the fly in the Stored proc and 'execute(@.SQLString)'. This will be
> executed like an ad-hoc query? I agree with all these sugestions, I just
> can't get by the fact that there is no locking issue because there is only
> one connection and one recordset used at a time during the batch. If Sql
> Server takes up all that memory, why should it get slower? Is it that
> because
> of this poor design and implementation (no indexes, fragmented heap
> tables,poorly coded stored procedures) SQL is using it up in the cache?
> What
> would be the best counters to tell what SQL has free of the memory it
> grabs?
>|||The machine is 2 processor P4 with 2 gig ram and a SCSI 3 disk raid 5
with 32 meg ram. SQL has access to all 2 gig. They tried setting it to
1.2gig and it didn't make a difference. So I am told! What would be the best
counters for this issue in SQL Profiler and Perfmon? Can you schedual SQL
Profiler?|||Just FYI a 3 disk Raid 5 is the worst performing array there is for write
operations if the log file is also on the array. Are you sure there are no
other apps or services running on this machine? I don't know if you can
schedule profiler or not but you can schedule a trace. With sp_tracecreate
and it's associated sp's you can create a trace that writes to a file and
schedule that any time you want in sql agent. Maybe these links can give
you some thing to start with in dealing with performance.
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:F3BB4B49-44CF-457D-BABC-A4C396057B42@.microsoft.com...
> The machine is 2 processor P4 with 2 gig ram and a SCSI 3 disk raid 5
> with 32 meg ram. SQL has access to all 2 gig. They tried setting it to
> 1.2gig and it didn't make a difference. So I am told! What would be the
> best
> counters for this issue in SQL Profiler and Perfmon? Can you schedual SQL
> Profiler?
>