I am using SQL 2K.
Size of the SQL Server error log file will degrade the performance of SQL
Server.
I am talking about log file which I cleared using SP_CYCLE_ERRORLOG.
Thanks,
JJ
Recycle more often then. How did you determine that it "degraded"
performance?
bert
|||Sorry,
It was my quetion as whether it will degrade the performance or not?
Thanks,
JJ
"bert" <bertcord@.gmail.com> wrote in message
news:1107297013.482698.267950@.l41g2000cwc.googlegr oups.com...
> Recycle more often then. How did you determine that it "degraded"
> performance?
> bert
>
|||Hi
Not that I have ever found. Output to the log file is appended and is not
read by SQL Server itself.
The bigger issue is how do you view it. A 10Mb log file does take a while to
open in EM (or notepad).
We cycle our error logs on our 400 servers every night. This helps us to
keep the Error log entries by day.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"John Smith" <JJSmith45@.hotmail.com> wrote in message
news:#bkfy5KCFHA.3524@.TK2MSFTNGP15.phx.gbl...
> Sorry,
> It was my quetion as whether it will degrade the performance or not?
> Thanks,
> JJ
> "bert" <bertcord@.gmail.com> wrote in message
> news:1107297013.482698.267950@.l41g2000cwc.googlegr oups.com...
>
|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:euXu08KCFHA.1936@.TK2MSFTNGP14.phx.gbl...
> Hi
> Not that I have ever found. Output to the log file is appended and is not
> read by SQL Server itself.
> The bigger issue is how do you view it. A 10Mb log file does take a while
to
> open in EM (or notepad).
> We cycle our error logs on our 400 servers every night. This helps us to
> keep the Error log entries by day.
>
We do it on the first of the month. But same idea.. keep it somewhat
reasonable and have enough "old" ones to troubleshoot problems.
|||Another helpful hint: either by registry key or by using SQLEM, you can
modify the properties of the SQL Error logs to increase the number retained.
We set ours to 32 and recycle at 11:59 PM every night.
Another thing to consider, most of what is output to the SQL Server Error
logs also ends up in the server's Application Event log; so, you might want
to increase the size of that log too.
Finally, if you have your security settings to Audit all logins, both the
SQL Server Error logs and the system's Application Event logs can be
enourmous. We currently only set for failed login attempts.
Sincerely,
Anthony Thomas
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:EthMd.26166$ZD1.5393@.twister.nyroc.rr.com...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:euXu08KCFHA.1936@.TK2MSFTNGP14.phx.gbl...
> Hi
> Not that I have ever found. Output to the log file is appended and is not
> read by SQL Server itself.
> The bigger issue is how do you view it. A 10Mb log file does take a while
to
> open in EM (or notepad).
> We cycle our error logs on our 400 servers every night. This helps us to
> keep the Error log entries by day.
>
We do it on the first of the month. But same idea.. keep it somewhat
reasonable and have enough "old" ones to troubleshoot problems.
Showing posts with label size. Show all posts
Showing posts with label size. Show all posts
Tuesday, March 20, 2012
PErformance and SQL Server Error Log
I am using SQL 2K.
Size of the SQL Server error log file will degrade the performance of SQL
Server.
I am talking about log file which I cleared using SP_CYCLE_ERRORLOG.
Thanks,
JJRecycle more often then. How did you determine that it "degraded"
performance?
bert|||Sorry,
It was my quetion as whether it will degrade the performance or not?
Thanks,
JJ
"bert" <bertcord@.gmail.com> wrote in message
news:1107297013.482698.267950@.l41g2000cwc.googlegroups.com...
> Recycle more often then. How did you determine that it "degraded"
> performance?
> bert
>|||Hi
Not that I have ever found. Output to the log file is appended and is not
read by SQL Server itself.
The bigger issue is how do you view it. A 10Mb log file does take a while to
open in EM (or notepad).
We cycle our error logs on our 400 servers every night. This helps us to
keep the Error log entries by day.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"John Smith" <JJSmith45@.hotmail.com> wrote in message
news:#bkfy5KCFHA.3524@.TK2MSFTNGP15.phx.gbl...
> Sorry,
> It was my quetion as whether it will degrade the performance or not?
> Thanks,
> JJ
> "bert" <bertcord@.gmail.com> wrote in message
> news:1107297013.482698.267950@.l41g2000cwc.googlegroups.com...
> > Recycle more often then. How did you determine that it "degraded"
> > performance?
> >
> > bert
> >
>|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:euXu08KCFHA.1936@.TK2MSFTNGP14.phx.gbl...
> Hi
> Not that I have ever found. Output to the log file is appended and is not
> read by SQL Server itself.
> The bigger issue is how do you view it. A 10Mb log file does take a while
to
> open in EM (or notepad).
> We cycle our error logs on our 400 servers every night. This helps us to
> keep the Error log entries by day.
>
We do it on the first of the month. But same idea.. keep it somewhat
reasonable and have enough "old" ones to troubleshoot problems.|||Another helpful hint: either by registry key or by using SQLEM, you can
modify the properties of the SQL Error logs to increase the number retained.
We set ours to 32 and recycle at 11:59 PM every night.
Another thing to consider, most of what is output to the SQL Server Error
logs also ends up in the server's Application Event log; so, you might want
to increase the size of that log too.
Finally, if you have your security settings to Audit all logins, both the
SQL Server Error logs and the system's Application Event logs can be
enourmous. We currently only set for failed login attempts.
Sincerely,
Anthony Thomas
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:EthMd.26166$ZD1.5393@.twister.nyroc.rr.com...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:euXu08KCFHA.1936@.TK2MSFTNGP14.phx.gbl...
> Hi
> Not that I have ever found. Output to the log file is appended and is not
> read by SQL Server itself.
> The bigger issue is how do you view it. A 10Mb log file does take a while
to
> open in EM (or notepad).
> We cycle our error logs on our 400 servers every night. This helps us to
> keep the Error log entries by day.
>
We do it on the first of the month. But same idea.. keep it somewhat
reasonable and have enough "old" ones to troubleshoot problems.
Size of the SQL Server error log file will degrade the performance of SQL
Server.
I am talking about log file which I cleared using SP_CYCLE_ERRORLOG.
Thanks,
JJRecycle more often then. How did you determine that it "degraded"
performance?
bert|||Sorry,
It was my quetion as whether it will degrade the performance or not?
Thanks,
JJ
"bert" <bertcord@.gmail.com> wrote in message
news:1107297013.482698.267950@.l41g2000cwc.googlegroups.com...
> Recycle more often then. How did you determine that it "degraded"
> performance?
> bert
>|||Hi
Not that I have ever found. Output to the log file is appended and is not
read by SQL Server itself.
The bigger issue is how do you view it. A 10Mb log file does take a while to
open in EM (or notepad).
We cycle our error logs on our 400 servers every night. This helps us to
keep the Error log entries by day.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"John Smith" <JJSmith45@.hotmail.com> wrote in message
news:#bkfy5KCFHA.3524@.TK2MSFTNGP15.phx.gbl...
> Sorry,
> It was my quetion as whether it will degrade the performance or not?
> Thanks,
> JJ
> "bert" <bertcord@.gmail.com> wrote in message
> news:1107297013.482698.267950@.l41g2000cwc.googlegroups.com...
> > Recycle more often then. How did you determine that it "degraded"
> > performance?
> >
> > bert
> >
>|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:euXu08KCFHA.1936@.TK2MSFTNGP14.phx.gbl...
> Hi
> Not that I have ever found. Output to the log file is appended and is not
> read by SQL Server itself.
> The bigger issue is how do you view it. A 10Mb log file does take a while
to
> open in EM (or notepad).
> We cycle our error logs on our 400 servers every night. This helps us to
> keep the Error log entries by day.
>
We do it on the first of the month. But same idea.. keep it somewhat
reasonable and have enough "old" ones to troubleshoot problems.|||Another helpful hint: either by registry key or by using SQLEM, you can
modify the properties of the SQL Error logs to increase the number retained.
We set ours to 32 and recycle at 11:59 PM every night.
Another thing to consider, most of what is output to the SQL Server Error
logs also ends up in the server's Application Event log; so, you might want
to increase the size of that log too.
Finally, if you have your security settings to Audit all logins, both the
SQL Server Error logs and the system's Application Event logs can be
enourmous. We currently only set for failed login attempts.
Sincerely,
Anthony Thomas
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:EthMd.26166$ZD1.5393@.twister.nyroc.rr.com...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:euXu08KCFHA.1936@.TK2MSFTNGP14.phx.gbl...
> Hi
> Not that I have ever found. Output to the log file is appended and is not
> read by SQL Server itself.
> The bigger issue is how do you view it. A 10Mb log file does take a while
to
> open in EM (or notepad).
> We cycle our error logs on our 400 servers every night. This helps us to
> keep the Error log entries by day.
>
We do it on the first of the month. But same idea.. keep it somewhat
reasonable and have enough "old" ones to troubleshoot problems.
PErformance and SQL Server Error Log
I am using SQL 2K.
Size of the SQL Server error log file will degrade the performance of SQL
Server.
I am talking about log file which I cleared using SP_CYCLE_ERRORLOG.
Thanks,
JJRecycle more often then. How did you determine that it "degraded"
performance?
bert|||Sorry,
It was my quetion as whether it will degrade the performance or not?
Thanks,
JJ
"bert" <bertcord@.gmail.com> wrote in message
news:1107297013.482698.267950@.l41g2000cwc.googlegroups.com...
> Recycle more often then. How did you determine that it "degraded"
> performance?
> bert
>|||Hi
Not that I have ever found. Output to the log file is appended and is not
read by SQL Server itself.
The bigger issue is how do you view it. A 10Mb log file does take a while to
open in EM (or notepad).
We cycle our error logs on our 400 servers every night. This helps us to
keep the Error log entries by day.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"John Smith" <JJSmith45@.hotmail.com> wrote in message
news:#bkfy5KCFHA.3524@.TK2MSFTNGP15.phx.gbl...
> Sorry,
> It was my quetion as whether it will degrade the performance or not?
> Thanks,
> JJ
> "bert" <bertcord@.gmail.com> wrote in message
> news:1107297013.482698.267950@.l41g2000cwc.googlegroups.com...
>|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:euXu08KCFHA.1936@.TK2MSFTNGP14.phx.gbl...
> Hi
> Not that I have ever found. Output to the log file is appended and is not
> read by SQL Server itself.
> The bigger issue is how do you view it. A 10Mb log file does take a while
to
> open in EM (or notepad).
> We cycle our error logs on our 400 servers every night. This helps us to
> keep the Error log entries by day.
>
We do it on the first of the month. But same idea.. keep it somewhat
reasonable and have enough "old" ones to troubleshoot problems.|||Another helpful hint: either by registry key or by using SQLEM, you can
modify the properties of the SQL Error logs to increase the number retained.
We set ours to 32 and recycle at 11:59 PM every night.
Another thing to consider, most of what is output to the SQL Server Error
logs also ends up in the server's Application Event log; so, you might want
to increase the size of that log too.
Finally, if you have your security settings to Audit all logins, both the
SQL Server Error logs and the system's Application Event logs can be
enourmous. We currently only set for failed login attempts.
Sincerely,
Anthony Thomas
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:EthMd.26166$ZD1.5393@.twister.nyroc.rr.com...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:euXu08KCFHA.1936@.TK2MSFTNGP14.phx.gbl...
> Hi
> Not that I have ever found. Output to the log file is appended and is not
> read by SQL Server itself.
> The bigger issue is how do you view it. A 10Mb log file does take a while
to
> open in EM (or notepad).
> We cycle our error logs on our 400 servers every night. This helps us to
> keep the Error log entries by day.
>
We do it on the first of the month. But same idea.. keep it somewhat
reasonable and have enough "old" ones to troubleshoot problems.
Size of the SQL Server error log file will degrade the performance of SQL
Server.
I am talking about log file which I cleared using SP_CYCLE_ERRORLOG.
Thanks,
JJRecycle more often then. How did you determine that it "degraded"
performance?
bert|||Sorry,
It was my quetion as whether it will degrade the performance or not?
Thanks,
JJ
"bert" <bertcord@.gmail.com> wrote in message
news:1107297013.482698.267950@.l41g2000cwc.googlegroups.com...
> Recycle more often then. How did you determine that it "degraded"
> performance?
> bert
>|||Hi
Not that I have ever found. Output to the log file is appended and is not
read by SQL Server itself.
The bigger issue is how do you view it. A 10Mb log file does take a while to
open in EM (or notepad).
We cycle our error logs on our 400 servers every night. This helps us to
keep the Error log entries by day.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"John Smith" <JJSmith45@.hotmail.com> wrote in message
news:#bkfy5KCFHA.3524@.TK2MSFTNGP15.phx.gbl...
> Sorry,
> It was my quetion as whether it will degrade the performance or not?
> Thanks,
> JJ
> "bert" <bertcord@.gmail.com> wrote in message
> news:1107297013.482698.267950@.l41g2000cwc.googlegroups.com...
>|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:euXu08KCFHA.1936@.TK2MSFTNGP14.phx.gbl...
> Hi
> Not that I have ever found. Output to the log file is appended and is not
> read by SQL Server itself.
> The bigger issue is how do you view it. A 10Mb log file does take a while
to
> open in EM (or notepad).
> We cycle our error logs on our 400 servers every night. This helps us to
> keep the Error log entries by day.
>
We do it on the first of the month. But same idea.. keep it somewhat
reasonable and have enough "old" ones to troubleshoot problems.|||Another helpful hint: either by registry key or by using SQLEM, you can
modify the properties of the SQL Error logs to increase the number retained.
We set ours to 32 and recycle at 11:59 PM every night.
Another thing to consider, most of what is output to the SQL Server Error
logs also ends up in the server's Application Event log; so, you might want
to increase the size of that log too.
Finally, if you have your security settings to Audit all logins, both the
SQL Server Error logs and the system's Application Event logs can be
enourmous. We currently only set for failed login attempts.
Sincerely,
Anthony Thomas
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:EthMd.26166$ZD1.5393@.twister.nyroc.rr.com...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:euXu08KCFHA.1936@.TK2MSFTNGP14.phx.gbl...
> Hi
> Not that I have ever found. Output to the log file is appended and is not
> read by SQL Server itself.
> The bigger issue is how do you view it. A 10Mb log file does take a while
to
> open in EM (or notepad).
> We cycle our error logs on our 400 servers every night. This helps us to
> keep the Error log entries by day.
>
We do it on the first of the month. But same idea.. keep it somewhat
reasonable and have enough "old" ones to troubleshoot problems.
Performance and Sizing question
Dear experts,
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size ?
2. Memory - minimum and maximum for sql server ?
3. Procedure cahe - size ?
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!
Hi
Check out http://www.sql-server-performance.co...erformance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/tg...568550-7347917
The Guru's Guide to SQL Server Architecture and Internals
http://www.awprofessional.com/catalo...7-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServe...588/45588.html
Others inline:
"AC" wrote:
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size ?
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file
> 2. Memory - minimum and maximum for sql server ?
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
http://www.windowsitpro.com/SQLServe...156/45156.html
http://www.windowsitpro.com/SQLServe...155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.
> 3. Procedure cahe - size ?
In general this is tuned while under load (firstly load testing and then
live environment)
> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
http://www.windowsitpro.com/Articles...layTab=Article
http://www.windowsitpro.com/Articles...layTab=Article
http://www.windowsitpro.com/Articles...layTab=Article
> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.
> Thank you in advance!
John
|||http://www.sql-server-performance.co...nitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size ?
> 2. Memory - minimum and maximum for sql server ?
> 3. Procedure cahe - size ?
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size ?
2. Memory - minimum and maximum for sql server ?
3. Procedure cahe - size ?
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!
Hi
Check out http://www.sql-server-performance.co...erformance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/tg...568550-7347917
The Guru's Guide to SQL Server Architecture and Internals
http://www.awprofessional.com/catalo...7-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServe...588/45588.html
Others inline:
"AC" wrote:
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size ?
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file
> 2. Memory - minimum and maximum for sql server ?
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
http://www.windowsitpro.com/SQLServe...156/45156.html
http://www.windowsitpro.com/SQLServe...155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.
> 3. Procedure cahe - size ?
In general this is tuned while under load (firstly load testing and then
live environment)
> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
http://www.windowsitpro.com/Articles...layTab=Article
http://www.windowsitpro.com/Articles...layTab=Article
http://www.windowsitpro.com/Articles...layTab=Article
> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.
> Thank you in advance!
John
|||http://www.sql-server-performance.co...nitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size ?
> 2. Memory - minimum and maximum for sql server ?
> 3. Procedure cahe - size ?
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!
Performance and Sizing question
Dear experts,
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size '
2. Memory - minimum and maximum for sql server '
3. Procedure cahe - size '
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!Hi
Check out http://www.sql-server-performance.c...performance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/t...6568550-7347917
The Guru's Guide to SQL Server Architecture and Internals
http://www.awprofessional.com/catal...37-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServ...5588/45588.html
Others inline:
"AC" wrote:
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3
G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file
> 2. Memory - minimum and maximum for sql server '
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/d...br />
8dbn.asp
http://www.windowsitpro.com/SQLServ...5156/45156.html
http://www.windowsitpro.com/SQLServ...5155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.
> 3. Procedure cahe - size '
In general this is tuned while under load (firstly load testing and then
live environment)
> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39158&DisplayTab=Article[
/url]
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=45154&DisplayTab=Article[
/url]
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39157&DisplayTab=Article[
/url]
> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.
> Thank you in advance!
John|||http://www.sql-server-performance.c...onitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
> 2. Memory - minimum and maximum for sql server '
> 3. Procedure cahe - size '
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size '
2. Memory - minimum and maximum for sql server '
3. Procedure cahe - size '
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!Hi
Check out http://www.sql-server-performance.c...performance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/t...6568550-7347917
The Guru's Guide to SQL Server Architecture and Internals
http://www.awprofessional.com/catal...37-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServ...5588/45588.html
Others inline:
"AC" wrote:
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3
G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file
> 2. Memory - minimum and maximum for sql server '
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/d...br />
8dbn.asp
http://www.windowsitpro.com/SQLServ...5156/45156.html
http://www.windowsitpro.com/SQLServ...5155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.
> 3. Procedure cahe - size '
In general this is tuned while under load (firstly load testing and then
live environment)
> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39158&DisplayTab=Article[
/url]
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=45154&DisplayTab=Article[
/url]
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39157&DisplayTab=Article[
/url]
> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.
> Thank you in advance!
John|||http://www.sql-server-performance.c...onitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
> 2. Memory - minimum and maximum for sql server '
> 3. Procedure cahe - size '
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!
Performance and Sizing question
Dear experts,
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size '
2. Memory - minimum and maximum for sql server '
3. Procedure cahe - size '
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!Hi
Check out http://www.sql-server-performance.com/articles_performance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/tg/detail/-/1555582419/104-6568550-7347917
The Guru's Guide to SQL Server Architecture and Internal
http://www.awprofessional.com/catalog/product.asp?product_id={FA365B66-8A6F-400E-A637-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45588/45588.html
Others inline:
"AC" wrote:
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file
> 2. Memory - minimum and maximum for sql server '
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45156/45156.html
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.
> 3. Procedure cahe - size '
In general this is tuned while under load (firstly load testing and then
live environment)
> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39158&DisplayTab=Article
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=45154&DisplayTab=Article
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39157&DisplayTab=Article
> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.
> Thank you in advance!
John|||http://www.sql-server-performance.com/performance_monitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
> 2. Memory - minimum and maximum for sql server '
> 3. Procedure cahe - size '
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size '
2. Memory - minimum and maximum for sql server '
3. Procedure cahe - size '
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!Hi
Check out http://www.sql-server-performance.com/articles_performance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/tg/detail/-/1555582419/104-6568550-7347917
The Guru's Guide to SQL Server Architecture and Internal
http://www.awprofessional.com/catalog/product.asp?product_id={FA365B66-8A6F-400E-A637-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45588/45588.html
Others inline:
"AC" wrote:
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file
> 2. Memory - minimum and maximum for sql server '
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45156/45156.html
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.
> 3. Procedure cahe - size '
In general this is tuned while under load (firstly load testing and then
live environment)
> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39158&DisplayTab=Article
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=45154&DisplayTab=Article
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39157&DisplayTab=Article
> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.
> Thank you in advance!
John|||http://www.sql-server-performance.com/performance_monitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
> 2. Memory - minimum and maximum for sql server '
> 3. Procedure cahe - size '
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!
Monday, March 12, 2012
Performance and Index Size
Hello:
We are testing SQL Server performance on XML columns and we have poor
perfomance, compared with another database (DB2). My questions are: a) is
there something we can do to improve performance; b) is there anything we ca
n
do to decrease index size.
We used big XML documents (40 Kb), small - medium records (5000 records) and
JDBC.
Results are the following:
Insert 5000 records: 1,162,859 sec. (Oracle) vs. 383,953 sec. (DB2)
Counting 5000 records: 21,188 sec. (Oracle) vs. 1,265 sec. (DB2)
Index size: 1.66 GB (Oracle) vs. 700 Mb (DB2)
It's worth noting that given the size of XML documents, the full indexed
scenario (primary index and all secondary indexes defined) could not be
tested, because it took more than 45 min. to insert 5000 records and ended
with a disk full error, consuming more than 2.5 GB.
We tried two queries: insert and counting. To insert we used the following
instruction:
INSERT INTO TEST VALUES (?, ?) - first parameter is int, second a string
with XML content.
To count we used the following query:
SELECT COUNT(*) FROM (
SELECT info.value('
declare default element namespace "urn:hl7-org:v3";
(/ClinicalDocument/component/structuredBody/component[7]/section/entry/obser
vation[code/@.code="550.90"]/effectiveTime/@.value)[1]
', 'nvarchar(1000)') YR FROM test) T
GROUP BY YR
ORDER BY YR DESC;
Index were created with the following instructions:
CREATE PRIMARY XML INDEX PIdx_TEST_info ON test(info)
CREATE XML INDEX PIdx_TEST_info_PATH ON test(info)
USING XML INDEX PIdx_TEST_info
FOR PATH
Thanks in advance for your help,
JavierHello Javier,
a.) Yes, defer creating the index until after all of your data has been inse
rted.
b.) Yes, only define the indexes that you know you are going to need.
Okay, so I know that A isn't a great answer, but honestly, it might be the
best choice you have here. Remember that the primary XML index persists the
node table to index space so every insert has to do the expensive parsing
and then maintain the index. You might be better of dropping and recreating
it after each bulk insert.
Schema binding make help the index be more space efficient due to typing,
but it looks like you're storing HL7 data. I can't imagine that SQL Server
is going to enjoy that schema very much.
Once you have the primary XML index beaten into submission, then you really
should decide and test if you need all of the other XML indexes or not. Its
very wasteful to define an index you don't need or use.
Its really not the size that's the issue here so much as it is the number
of elements and attributes within each instance and the relative amount of
useful data per node. Again if I'm guessing right in your 40k of data, you
probably have hundreds of elements and attributes. Since each of these becom
es
a row in the node table, you're probably generating lots of rows with little
data in them.
I'd also have to wonder how much or little your JDBC driver is helping you
here, but that's topic for another NG.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi Javier
I would be interested in your test setup. Could you please contact me at my
email (remove the online from the address)?
Thanks
Michael
"Javier Diaz" <JavierDiaz@.discussions.microsoft.com> wrote in message
news:DF70B1FD-B8A5-4F6F-8CAA-865104B6572E@.microsoft.com...
> Hello:
> We are testing SQL Server performance on XML columns and we have poor
> perfomance, compared with another database (DB2). My questions are: a) is
> there something we can do to improve performance; b) is there anything we
> can
> do to decrease index size.
> We used big XML documents (40 Kb), small - medium records (5000 records)
> and
> JDBC.
> Results are the following:
> Insert 5000 records: 1,162,859 sec. (Oracle) vs. 383,953 sec. (DB2)
> Counting 5000 records: 21,188 sec. (Oracle) vs. 1,265 sec. (DB2)
> Index size: 1.66 GB (Oracle) vs. 700 Mb (DB2)
> It's worth noting that given the size of XML documents, the full indexed
> scenario (primary index and all secondary indexes defined) could not be
> tested, because it took more than 45 min. to insert 5000 records and ended
> with a disk full error, consuming more than 2.5 GB.
> We tried two queries: insert and counting. To insert we used the following
> instruction:
> INSERT INTO TEST VALUES (?, ?) - first parameter is int, second a string
> with XML content.
> To count we used the following query:
> SELECT COUNT(*) FROM (
> SELECT info.value('
> declare default element namespace "urn:hl7-org:v3";
> (/ClinicalDocument/component/structuredBody/component[7]/section/entry/obs
ervation[code/@.code="550.90"]/effectiveTime/@.value)[1]
> ', 'nvarchar(1000)') YR FROM test) T
> GROUP BY YR
> ORDER BY YR DESC;
> Index were created with the following instructions:
> CREATE PRIMARY XML INDEX PIdx_TEST_info ON test(info)
> CREATE XML INDEX PIdx_TEST_info_PATH ON test(info)
> USING XML INDEX PIdx_TEST_info
> FOR PATH
> Thanks in advance for your help,
> Javier
>
We are testing SQL Server performance on XML columns and we have poor
perfomance, compared with another database (DB2). My questions are: a) is
there something we can do to improve performance; b) is there anything we ca
n
do to decrease index size.
We used big XML documents (40 Kb), small - medium records (5000 records) and
JDBC.
Results are the following:
Insert 5000 records: 1,162,859 sec. (Oracle) vs. 383,953 sec. (DB2)
Counting 5000 records: 21,188 sec. (Oracle) vs. 1,265 sec. (DB2)
Index size: 1.66 GB (Oracle) vs. 700 Mb (DB2)
It's worth noting that given the size of XML documents, the full indexed
scenario (primary index and all secondary indexes defined) could not be
tested, because it took more than 45 min. to insert 5000 records and ended
with a disk full error, consuming more than 2.5 GB.
We tried two queries: insert and counting. To insert we used the following
instruction:
INSERT INTO TEST VALUES (?, ?) - first parameter is int, second a string
with XML content.
To count we used the following query:
SELECT COUNT(*) FROM (
SELECT info.value('
declare default element namespace "urn:hl7-org:v3";
(/ClinicalDocument/component/structuredBody/component[7]/section/entry/obser
vation[code/@.code="550.90"]/effectiveTime/@.value)[1]
', 'nvarchar(1000)') YR FROM test) T
GROUP BY YR
ORDER BY YR DESC;
Index were created with the following instructions:
CREATE PRIMARY XML INDEX PIdx_TEST_info ON test(info)
CREATE XML INDEX PIdx_TEST_info_PATH ON test(info)
USING XML INDEX PIdx_TEST_info
FOR PATH
Thanks in advance for your help,
JavierHello Javier,
a.) Yes, defer creating the index until after all of your data has been inse
rted.
b.) Yes, only define the indexes that you know you are going to need.
Okay, so I know that A isn't a great answer, but honestly, it might be the
best choice you have here. Remember that the primary XML index persists the
node table to index space so every insert has to do the expensive parsing
and then maintain the index. You might be better of dropping and recreating
it after each bulk insert.
Schema binding make help the index be more space efficient due to typing,
but it looks like you're storing HL7 data. I can't imagine that SQL Server
is going to enjoy that schema very much.
Once you have the primary XML index beaten into submission, then you really
should decide and test if you need all of the other XML indexes or not. Its
very wasteful to define an index you don't need or use.
Its really not the size that's the issue here so much as it is the number
of elements and attributes within each instance and the relative amount of
useful data per node. Again if I'm guessing right in your 40k of data, you
probably have hundreds of elements and attributes. Since each of these becom
es
a row in the node table, you're probably generating lots of rows with little
data in them.
I'd also have to wonder how much or little your JDBC driver is helping you
here, but that's topic for another NG.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi Javier
I would be interested in your test setup. Could you please contact me at my
email (remove the online from the address)?
Thanks
Michael
"Javier Diaz" <JavierDiaz@.discussions.microsoft.com> wrote in message
news:DF70B1FD-B8A5-4F6F-8CAA-865104B6572E@.microsoft.com...
> Hello:
> We are testing SQL Server performance on XML columns and we have poor
> perfomance, compared with another database (DB2). My questions are: a) is
> there something we can do to improve performance; b) is there anything we
> can
> do to decrease index size.
> We used big XML documents (40 Kb), small - medium records (5000 records)
> and
> JDBC.
> Results are the following:
> Insert 5000 records: 1,162,859 sec. (Oracle) vs. 383,953 sec. (DB2)
> Counting 5000 records: 21,188 sec. (Oracle) vs. 1,265 sec. (DB2)
> Index size: 1.66 GB (Oracle) vs. 700 Mb (DB2)
> It's worth noting that given the size of XML documents, the full indexed
> scenario (primary index and all secondary indexes defined) could not be
> tested, because it took more than 45 min. to insert 5000 records and ended
> with a disk full error, consuming more than 2.5 GB.
> We tried two queries: insert and counting. To insert we used the following
> instruction:
> INSERT INTO TEST VALUES (?, ?) - first parameter is int, second a string
> with XML content.
> To count we used the following query:
> SELECT COUNT(*) FROM (
> SELECT info.value('
> declare default element namespace "urn:hl7-org:v3";
> (/ClinicalDocument/component/structuredBody/component[7]/section/entry/obs
ervation[code/@.code="550.90"]/effectiveTime/@.value)[1]
> ', 'nvarchar(1000)') YR FROM test) T
> GROUP BY YR
> ORDER BY YR DESC;
> Index were created with the following instructions:
> CREATE PRIMARY XML INDEX PIdx_TEST_info ON test(info)
> CREATE XML INDEX PIdx_TEST_info_PATH ON test(info)
> USING XML INDEX PIdx_TEST_info
> FOR PATH
> Thanks in advance for your help,
> Javier
>
Performance and Index Size
Hello:
We are testing SQL Server performance on XML columns and we have poor
perfomance, compared with another database (DB2). My questions are: a) is
there something we can do to improve performance; b) is there anything we can
do to decrease index size.
We used big XML documents (40 Kb), small - medium records (5000 records) and
JDBC.
Results are the following:
Insert 5000 records: 1,162,859 sec. (Oracle) vs. 383,953 sec. (DB2)
Counting 5000 records: 21,188 sec. (Oracle) vs. 1,265 sec. (DB2)
Index size: 1.66 GB (Oracle) vs. 700 Mb (DB2)
It's worth noting that given the size of XML documents, the full indexed
scenario (primary index and all secondary indexes defined) could not be
tested, because it took more than 45 min. to insert 5000 records and ended
with a disk full error, consuming more than 2.5 GB.
We tried two queries: insert and counting. To insert we used the following
instruction:
INSERT INTO TEST VALUES (?, ?) - first parameter is int, second a string
with XML content.
To count we used the following query:
SELECT COUNT(*) FROM (
SELECT info.value('
declare default element namespace "urn:hl7-org:v3";
(/ClinicalDocument/component/structuredBody/component[7]/section/entry/observation[code/@.code="550.90"]/effectiveTime/@.value)[1]
', 'nvarchar(1000)') YR FROM test) T
GROUP BY YR
ORDER BY YR DESC;
Index were created with the following instructions:
CREATE PRIMARY XML INDEX PIdx_TEST_info ON test(info)
CREATE XML INDEX PIdx_TEST_info_PATH ON test(info)
USING XML INDEX PIdx_TEST_info
FOR PATH
Thanks in advance for your help,
Javier
Hello Javier,
a.) Yes, defer creating the index until after all of your data has been inserted.
b.) Yes, only define the indexes that you know you are going to need.
Okay, so I know that A isn't a great answer, but honestly, it might be the
best choice you have here. Remember that the primary XML index persists the
node table to index space so every insert has to do the expensive parsing
and then maintain the index. You might be better of dropping and recreating
it after each bulk insert.
Schema binding make help the index be more space efficient due to typing,
but it looks like you're storing HL7 data. I can't imagine that SQL Server
is going to enjoy that schema very much.
Once you have the primary XML index beaten into submission, then you really
should decide and test if you need all of the other XML indexes or not. Its
very wasteful to define an index you don't need or use.
Its really not the size that's the issue here so much as it is the number
of elements and attributes within each instance and the relative amount of
useful data per node. Again if I'm guessing right in your 40k of data, you
probably have hundreds of elements and attributes. Since each of these becomes
a row in the node table, you're probably generating lots of rows with little
data in them.
I'd also have to wonder how much or little your JDBC driver is helping you
here, but that's topic for another NG.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Hi Javier
I would be interested in your test setup. Could you please contact me at my
email (remove the online from the address)?
Thanks
Michael
"Javier Diaz" <JavierDiaz@.discussions.microsoft.com> wrote in message
news:DF70B1FD-B8A5-4F6F-8CAA-865104B6572E@.microsoft.com...
> Hello:
> We are testing SQL Server performance on XML columns and we have poor
> perfomance, compared with another database (DB2). My questions are: a) is
> there something we can do to improve performance; b) is there anything we
> can
> do to decrease index size.
> We used big XML documents (40 Kb), small - medium records (5000 records)
> and
> JDBC.
> Results are the following:
> Insert 5000 records: 1,162,859 sec. (Oracle) vs. 383,953 sec. (DB2)
> Counting 5000 records: 21,188 sec. (Oracle) vs. 1,265 sec. (DB2)
> Index size: 1.66 GB (Oracle) vs. 700 Mb (DB2)
> It's worth noting that given the size of XML documents, the full indexed
> scenario (primary index and all secondary indexes defined) could not be
> tested, because it took more than 45 min. to insert 5000 records and ended
> with a disk full error, consuming more than 2.5 GB.
> We tried two queries: insert and counting. To insert we used the following
> instruction:
> INSERT INTO TEST VALUES (?, ?) - first parameter is int, second a string
> with XML content.
> To count we used the following query:
> SELECT COUNT(*) FROM (
> SELECT info.value('
> declare default element namespace "urn:hl7-org:v3";
> (/ClinicalDocument/component/structuredBody/component[7]/section/entry/observation[code/@.code="550.90"]/effectiveTime/@.value)[1]
> ', 'nvarchar(1000)') YR FROM test) T
> GROUP BY YR
> ORDER BY YR DESC;
> Index were created with the following instructions:
> CREATE PRIMARY XML INDEX PIdx_TEST_info ON test(info)
> CREATE XML INDEX PIdx_TEST_info_PATH ON test(info)
> USING XML INDEX PIdx_TEST_info
> FOR PATH
> Thanks in advance for your help,
> Javier
>
We are testing SQL Server performance on XML columns and we have poor
perfomance, compared with another database (DB2). My questions are: a) is
there something we can do to improve performance; b) is there anything we can
do to decrease index size.
We used big XML documents (40 Kb), small - medium records (5000 records) and
JDBC.
Results are the following:
Insert 5000 records: 1,162,859 sec. (Oracle) vs. 383,953 sec. (DB2)
Counting 5000 records: 21,188 sec. (Oracle) vs. 1,265 sec. (DB2)
Index size: 1.66 GB (Oracle) vs. 700 Mb (DB2)
It's worth noting that given the size of XML documents, the full indexed
scenario (primary index and all secondary indexes defined) could not be
tested, because it took more than 45 min. to insert 5000 records and ended
with a disk full error, consuming more than 2.5 GB.
We tried two queries: insert and counting. To insert we used the following
instruction:
INSERT INTO TEST VALUES (?, ?) - first parameter is int, second a string
with XML content.
To count we used the following query:
SELECT COUNT(*) FROM (
SELECT info.value('
declare default element namespace "urn:hl7-org:v3";
(/ClinicalDocument/component/structuredBody/component[7]/section/entry/observation[code/@.code="550.90"]/effectiveTime/@.value)[1]
', 'nvarchar(1000)') YR FROM test) T
GROUP BY YR
ORDER BY YR DESC;
Index were created with the following instructions:
CREATE PRIMARY XML INDEX PIdx_TEST_info ON test(info)
CREATE XML INDEX PIdx_TEST_info_PATH ON test(info)
USING XML INDEX PIdx_TEST_info
FOR PATH
Thanks in advance for your help,
Javier
Hello Javier,
a.) Yes, defer creating the index until after all of your data has been inserted.
b.) Yes, only define the indexes that you know you are going to need.
Okay, so I know that A isn't a great answer, but honestly, it might be the
best choice you have here. Remember that the primary XML index persists the
node table to index space so every insert has to do the expensive parsing
and then maintain the index. You might be better of dropping and recreating
it after each bulk insert.
Schema binding make help the index be more space efficient due to typing,
but it looks like you're storing HL7 data. I can't imagine that SQL Server
is going to enjoy that schema very much.
Once you have the primary XML index beaten into submission, then you really
should decide and test if you need all of the other XML indexes or not. Its
very wasteful to define an index you don't need or use.
Its really not the size that's the issue here so much as it is the number
of elements and attributes within each instance and the relative amount of
useful data per node. Again if I'm guessing right in your 40k of data, you
probably have hundreds of elements and attributes. Since each of these becomes
a row in the node table, you're probably generating lots of rows with little
data in them.
I'd also have to wonder how much or little your JDBC driver is helping you
here, but that's topic for another NG.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Hi Javier
I would be interested in your test setup. Could you please contact me at my
email (remove the online from the address)?
Thanks
Michael
"Javier Diaz" <JavierDiaz@.discussions.microsoft.com> wrote in message
news:DF70B1FD-B8A5-4F6F-8CAA-865104B6572E@.microsoft.com...
> Hello:
> We are testing SQL Server performance on XML columns and we have poor
> perfomance, compared with another database (DB2). My questions are: a) is
> there something we can do to improve performance; b) is there anything we
> can
> do to decrease index size.
> We used big XML documents (40 Kb), small - medium records (5000 records)
> and
> JDBC.
> Results are the following:
> Insert 5000 records: 1,162,859 sec. (Oracle) vs. 383,953 sec. (DB2)
> Counting 5000 records: 21,188 sec. (Oracle) vs. 1,265 sec. (DB2)
> Index size: 1.66 GB (Oracle) vs. 700 Mb (DB2)
> It's worth noting that given the size of XML documents, the full indexed
> scenario (primary index and all secondary indexes defined) could not be
> tested, because it took more than 45 min. to insert 5000 records and ended
> with a disk full error, consuming more than 2.5 GB.
> We tried two queries: insert and counting. To insert we used the following
> instruction:
> INSERT INTO TEST VALUES (?, ?) - first parameter is int, second a string
> with XML content.
> To count we used the following query:
> SELECT COUNT(*) FROM (
> SELECT info.value('
> declare default element namespace "urn:hl7-org:v3";
> (/ClinicalDocument/component/structuredBody/component[7]/section/entry/observation[code/@.code="550.90"]/effectiveTime/@.value)[1]
> ', 'nvarchar(1000)') YR FROM test) T
> GROUP BY YR
> ORDER BY YR DESC;
> Index were created with the following instructions:
> CREATE PRIMARY XML INDEX PIdx_TEST_info ON test(info)
> CREATE XML INDEX PIdx_TEST_info_PATH ON test(info)
> USING XML INDEX PIdx_TEST_info
> FOR PATH
> Thanks in advance for your help,
> Javier
>
Subscribe to:
Posts (Atom)