We have some internal applications that rely heavily on using temporary
databases with a lot of data. I have noticed that when these applications
run, they seem to starve the system of RAM. This is indicated by an
increase in the RAM used by the SQL process and a heavy amount of disk I/O.
My assumption is that when the temp db's are created, SQL tries to get as
much RAM from the OS as possible which creates swapping non-SQL memory to
the pagefile and SQL internally needs to free RAM and thus needs to write
cached data out to disk.
Questions:
1) Are my assumptions correct in the usage of the temp databases?
2) Are there any workarounds to this?
3) Since SQL optimizes RAM over time, I would guess that no matter what we
do, SQL will always fill up its internal RAM with cached data, thus adding
more RAM to the system won't resolve these symptoms, it will merely delay
them by taking more time to cache all the RAM. (Our applications are
already filling up SQL with 2 GB of RAM. I suspect moving to 4GB will not
have a significant improvement due to the memory address extensions. 64 bit
SQL would probably be our only solution to flood the system with enough RAM
that our apps can't fill up SQL's internal RAM usage, but 64 bit SQL and
enough RAM in a system for this would be significanlty expensive for our
needs.)
Thank You,Kevin,
What do you mean by Temp databases? Are you creating databases and then
dropping them? Or do you mean temporary tables being created in the tempdb
database? If you only have 2GB of ram and your using it all I would
suggest you set your max memory setting in sql server to around 1.5GB to
allow the OS and memtoleave areas some slack. If you are memory constrained
and it sounds like you are, then going to 4GB can make a huge difference.
You can set the /3gb switch to allow sql server use of up to 3GB and leave
1GB for the OS. This should alleviate most of the OS paging and sql server
can use it's caching mechanisims as designed without dealing with the OS
swap file.
Andrew J. Kelly SQL MVP
"Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
news:c5jhcm$42i$1@.grandcanyon.binc.net...
> We have some internal applications that rely heavily on using temporary
> databases with a lot of data. I have noticed that when these applications
> run, they seem to starve the system of RAM. This is indicated by an
> increase in the RAM used by the SQL process and a heavy amount of disk
I/O.
> My assumption is that when the temp db's are created, SQL tries to get as
> much RAM from the OS as possible which creates swapping non-SQL memory to
> the pagefile and SQL internally needs to free RAM and thus needs to write
> cached data out to disk.
> Questions:
> 1) Are my assumptions correct in the usage of the temp databases?
> 2) Are there any workarounds to this?
> 3) Since SQL optimizes RAM over time, I would guess that no matter what
we
> do, SQL will always fill up its internal RAM with cached data, thus adding
> more RAM to the system won't resolve these symptoms, it will merely delay
> them by taking more time to cache all the RAM. (Our applications are
> already filling up SQL with 2 GB of RAM. I suspect moving to 4GB will not
> have a significant improvement due to the memory address extensions. 64
bit
> SQL would probably be our only solution to flood the system with enough
RAM
> that our apps can't fill up SQL's internal RAM usage, but 64 bit SQL and
> enough RAM in a system for this would be significanlty expensive for our
> needs.)
>
> Thank You,
>|||Yes I was referring to creating databases then dropping them. In one
particular application, a lot of the stored procedures are running
algorithms that do this.
Thank You for the feedback. Now that we have a monitoring system in place,
I will experiment with restricting SQL to 1.5 GB or so and monitor memory
swapping and related disk I/O.
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:OzqSg7oIEHA.3276@.TK2MSFTNGP09.phx.gbl...
> Kevin,
> What do you mean by Temp databases? Are you creating databases and then
> dropping them? Or do you mean temporary tables being created in the
tempdb
> database? If you only have 2GB of ram and your using it all I would
> suggest you set your max memory setting in sql server to around 1.5GB to
> allow the OS and memtoleave areas some slack. If you are memory
constrained
> and it sounds like you are, then going to 4GB can make a huge difference.
> You can set the /3gb switch to allow sql server use of up to 3GB and leave
> 1GB for the OS. This should alleviate most of the OS paging and sql
server
> can use it's caching mechanisims as designed without dealing with the OS
> swap file.
> --
> Andrew J. Kelly SQL MVP
>
> "Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
> news:c5jhcm$42i$1@.grandcanyon.binc.net...
applications[vbcol=seagreen]
> I/O.
as[vbcol=seagreen]
to[vbcol=seagreen]
write[vbcol=seagreen]
> we
adding[vbcol=seagreen]
delay[vbcol=seagreen]
not[vbcol=seagreen]
> bit
> RAM
>
Showing posts with label heavily. Show all posts
Showing posts with label heavily. Show all posts
Wednesday, March 28, 2012
performance Questions - Temp Databases
We have some internal applications that rely heavily on using temporary
databases with a lot of data. I have noticed that when these applications
run, they seem to starve the system of RAM. This is indicated by an
increase in the RAM used by the SQL process and a heavy amount of disk I/O.
My assumption is that when the temp db's are created, SQL tries to get as
much RAM from the OS as possible which creates swapping non-SQL memory to
the pagefile and SQL internally needs to free RAM and thus needs to write
cached data out to disk.
Questions:
1) Are my assumptions correct in the usage of the temp databases?
2) Are there any workarounds to this?
3) Since SQL optimizes RAM over time, I would guess that no matter what we
do, SQL will always fill up its internal RAM with cached data, thus adding
more RAM to the system won't resolve these symptoms, it will merely delay
them by taking more time to cache all the RAM. (Our applications are
already filling up SQL with 2 GB of RAM. I suspect moving to 4GB will not
have a significant improvement due to the memory address extensions. 64 bit
SQL would probably be our only solution to flood the system with enough RAM
that our apps can't fill up SQL's internal RAM usage, but 64 bit SQL and
enough RAM in a system for this would be significanlty expensive for our
needs.)
Thank You,Kevin,
What do you mean by Temp databases? Are you creating databases and then
dropping them? Or do you mean temporary tables being created in the tempdb
database? If you only have 2GB of ram and your using it all I would
suggest you set your max memory setting in sql server to around 1.5GB to
allow the OS and memtoleave areas some slack. If you are memory constrained
and it sounds like you are, then going to 4GB can make a huge difference.
You can set the /3gb switch to allow sql server use of up to 3GB and leave
1GB for the OS. This should alleviate most of the OS paging and sql server
can use it's caching mechanisims as designed without dealing with the OS
swap file.
--
Andrew J. Kelly SQL MVP
"Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
news:c5jhcm$42i$1@.grandcanyon.binc.net...
> We have some internal applications that rely heavily on using temporary
> databases with a lot of data. I have noticed that when these applications
> run, they seem to starve the system of RAM. This is indicated by an
> increase in the RAM used by the SQL process and a heavy amount of disk
I/O.
> My assumption is that when the temp db's are created, SQL tries to get as
> much RAM from the OS as possible which creates swapping non-SQL memory to
> the pagefile and SQL internally needs to free RAM and thus needs to write
> cached data out to disk.
> Questions:
> 1) Are my assumptions correct in the usage of the temp databases?
> 2) Are there any workarounds to this?
> 3) Since SQL optimizes RAM over time, I would guess that no matter what
we
> do, SQL will always fill up its internal RAM with cached data, thus adding
> more RAM to the system won't resolve these symptoms, it will merely delay
> them by taking more time to cache all the RAM. (Our applications are
> already filling up SQL with 2 GB of RAM. I suspect moving to 4GB will not
> have a significant improvement due to the memory address extensions. 64
bit
> SQL would probably be our only solution to flood the system with enough
RAM
> that our apps can't fill up SQL's internal RAM usage, but 64 bit SQL and
> enough RAM in a system for this would be significanlty expensive for our
> needs.)
>
> Thank You,
>|||Yes I was referring to creating databases then dropping them. In one
particular application, a lot of the stored procedures are running
algorithms that do this.
Thank You for the feedback. Now that we have a monitoring system in place,
I will experiment with restricting SQL to 1.5 GB or so and monitor memory
swapping and related disk I/O.
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:OzqSg7oIEHA.3276@.TK2MSFTNGP09.phx.gbl...
> Kevin,
> What do you mean by Temp databases? Are you creating databases and then
> dropping them? Or do you mean temporary tables being created in the
tempdb
> database? If you only have 2GB of ram and your using it all I would
> suggest you set your max memory setting in sql server to around 1.5GB to
> allow the OS and memtoleave areas some slack. If you are memory
constrained
> and it sounds like you are, then going to 4GB can make a huge difference.
> You can set the /3gb switch to allow sql server use of up to 3GB and leave
> 1GB for the OS. This should alleviate most of the OS paging and sql
server
> can use it's caching mechanisims as designed without dealing with the OS
> swap file.
> --
> Andrew J. Kelly SQL MVP
>
> "Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
> news:c5jhcm$42i$1@.grandcanyon.binc.net...
> > We have some internal applications that rely heavily on using temporary
> > databases with a lot of data. I have noticed that when these
applications
> > run, they seem to starve the system of RAM. This is indicated by an
> > increase in the RAM used by the SQL process and a heavy amount of disk
> I/O.
> > My assumption is that when the temp db's are created, SQL tries to get
as
> > much RAM from the OS as possible which creates swapping non-SQL memory
to
> > the pagefile and SQL internally needs to free RAM and thus needs to
write
> > cached data out to disk.
> >
> > Questions:
> > 1) Are my assumptions correct in the usage of the temp databases?
> > 2) Are there any workarounds to this?
> > 3) Since SQL optimizes RAM over time, I would guess that no matter what
> we
> > do, SQL will always fill up its internal RAM with cached data, thus
adding
> > more RAM to the system won't resolve these symptoms, it will merely
delay
> > them by taking more time to cache all the RAM. (Our applications are
> > already filling up SQL with 2 GB of RAM. I suspect moving to 4GB will
not
> > have a significant improvement due to the memory address extensions. 64
> bit
> > SQL would probably be our only solution to flood the system with enough
> RAM
> > that our apps can't fill up SQL's internal RAM usage, but 64 bit SQL and
> > enough RAM in a system for this would be significanlty expensive for our
> > needs.)
> >
> >
> > Thank You,
> >
> >
>sql
databases with a lot of data. I have noticed that when these applications
run, they seem to starve the system of RAM. This is indicated by an
increase in the RAM used by the SQL process and a heavy amount of disk I/O.
My assumption is that when the temp db's are created, SQL tries to get as
much RAM from the OS as possible which creates swapping non-SQL memory to
the pagefile and SQL internally needs to free RAM and thus needs to write
cached data out to disk.
Questions:
1) Are my assumptions correct in the usage of the temp databases?
2) Are there any workarounds to this?
3) Since SQL optimizes RAM over time, I would guess that no matter what we
do, SQL will always fill up its internal RAM with cached data, thus adding
more RAM to the system won't resolve these symptoms, it will merely delay
them by taking more time to cache all the RAM. (Our applications are
already filling up SQL with 2 GB of RAM. I suspect moving to 4GB will not
have a significant improvement due to the memory address extensions. 64 bit
SQL would probably be our only solution to flood the system with enough RAM
that our apps can't fill up SQL's internal RAM usage, but 64 bit SQL and
enough RAM in a system for this would be significanlty expensive for our
needs.)
Thank You,Kevin,
What do you mean by Temp databases? Are you creating databases and then
dropping them? Or do you mean temporary tables being created in the tempdb
database? If you only have 2GB of ram and your using it all I would
suggest you set your max memory setting in sql server to around 1.5GB to
allow the OS and memtoleave areas some slack. If you are memory constrained
and it sounds like you are, then going to 4GB can make a huge difference.
You can set the /3gb switch to allow sql server use of up to 3GB and leave
1GB for the OS. This should alleviate most of the OS paging and sql server
can use it's caching mechanisims as designed without dealing with the OS
swap file.
--
Andrew J. Kelly SQL MVP
"Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
news:c5jhcm$42i$1@.grandcanyon.binc.net...
> We have some internal applications that rely heavily on using temporary
> databases with a lot of data. I have noticed that when these applications
> run, they seem to starve the system of RAM. This is indicated by an
> increase in the RAM used by the SQL process and a heavy amount of disk
I/O.
> My assumption is that when the temp db's are created, SQL tries to get as
> much RAM from the OS as possible which creates swapping non-SQL memory to
> the pagefile and SQL internally needs to free RAM and thus needs to write
> cached data out to disk.
> Questions:
> 1) Are my assumptions correct in the usage of the temp databases?
> 2) Are there any workarounds to this?
> 3) Since SQL optimizes RAM over time, I would guess that no matter what
we
> do, SQL will always fill up its internal RAM with cached data, thus adding
> more RAM to the system won't resolve these symptoms, it will merely delay
> them by taking more time to cache all the RAM. (Our applications are
> already filling up SQL with 2 GB of RAM. I suspect moving to 4GB will not
> have a significant improvement due to the memory address extensions. 64
bit
> SQL would probably be our only solution to flood the system with enough
RAM
> that our apps can't fill up SQL's internal RAM usage, but 64 bit SQL and
> enough RAM in a system for this would be significanlty expensive for our
> needs.)
>
> Thank You,
>|||Yes I was referring to creating databases then dropping them. In one
particular application, a lot of the stored procedures are running
algorithms that do this.
Thank You for the feedback. Now that we have a monitoring system in place,
I will experiment with restricting SQL to 1.5 GB or so and monitor memory
swapping and related disk I/O.
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:OzqSg7oIEHA.3276@.TK2MSFTNGP09.phx.gbl...
> Kevin,
> What do you mean by Temp databases? Are you creating databases and then
> dropping them? Or do you mean temporary tables being created in the
tempdb
> database? If you only have 2GB of ram and your using it all I would
> suggest you set your max memory setting in sql server to around 1.5GB to
> allow the OS and memtoleave areas some slack. If you are memory
constrained
> and it sounds like you are, then going to 4GB can make a huge difference.
> You can set the /3gb switch to allow sql server use of up to 3GB and leave
> 1GB for the OS. This should alleviate most of the OS paging and sql
server
> can use it's caching mechanisims as designed without dealing with the OS
> swap file.
> --
> Andrew J. Kelly SQL MVP
>
> "Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
> news:c5jhcm$42i$1@.grandcanyon.binc.net...
> > We have some internal applications that rely heavily on using temporary
> > databases with a lot of data. I have noticed that when these
applications
> > run, they seem to starve the system of RAM. This is indicated by an
> > increase in the RAM used by the SQL process and a heavy amount of disk
> I/O.
> > My assumption is that when the temp db's are created, SQL tries to get
as
> > much RAM from the OS as possible which creates swapping non-SQL memory
to
> > the pagefile and SQL internally needs to free RAM and thus needs to
write
> > cached data out to disk.
> >
> > Questions:
> > 1) Are my assumptions correct in the usage of the temp databases?
> > 2) Are there any workarounds to this?
> > 3) Since SQL optimizes RAM over time, I would guess that no matter what
> we
> > do, SQL will always fill up its internal RAM with cached data, thus
adding
> > more RAM to the system won't resolve these symptoms, it will merely
delay
> > them by taking more time to cache all the RAM. (Our applications are
> > already filling up SQL with 2 GB of RAM. I suspect moving to 4GB will
not
> > have a significant improvement due to the memory address extensions. 64
> bit
> > SQL would probably be our only solution to flood the system with enough
> RAM
> > that our apps can't fill up SQL's internal RAM usage, but 64 bit SQL and
> > enough RAM in a system for this would be significanlty expensive for our
> > needs.)
> >
> >
> > Thank You,
> >
> >
>sql
performance Questions - Temp Databases
We have some internal applications that rely heavily on using temporary
databases with a lot of data. I have noticed that when these applications
run, they seem to starve the system of RAM. This is indicated by an
increase in the RAM used by the SQL process and a heavy amount of disk I/O.
My assumption is that when the temp db's are created, SQL tries to get as
much RAM from the OS as possible which creates swapping non-SQL memory to
the pagefile and SQL internally needs to free RAM and thus needs to write
cached data out to disk.
Questions:
1) Are my assumptions correct in the usage of the temp databases?
2) Are there any workarounds to this?
3) Since SQL optimizes RAM over time, I would guess that no matter what we
do, SQL will always fill up its internal RAM with cached data, thus adding
more RAM to the system won't resolve these symptoms, it will merely delay
them by taking more time to cache all the RAM. (Our applications are
already filling up SQL with 2 GB of RAM. I suspect moving to 4GB will not
have a significant improvement due to the memory address extensions. 64 bit
SQL would probably be our only solution to flood the system with enough RAM
that our apps can't fill up SQL's internal RAM usage, but 64 bit SQL and
enough RAM in a system for this would be significanlty expensive for our
needs.)
Thank You,
Kevin,
What do you mean by Temp databases? Are you creating databases and then
dropping them? Or do you mean temporary tables being created in the tempdb
database? If you only have 2GB of ram and your using it all I would
suggest you set your max memory setting in sql server to around 1.5GB to
allow the OS and memtoleave areas some slack. If you are memory constrained
and it sounds like you are, then going to 4GB can make a huge difference.
You can set the /3gb switch to allow sql server use of up to 3GB and leave
1GB for the OS. This should alleviate most of the OS paging and sql server
can use it's caching mechanisims as designed without dealing with the OS
swap file.
Andrew J. Kelly SQL MVP
"Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
news:c5jhcm$42i$1@.grandcanyon.binc.net...
> We have some internal applications that rely heavily on using temporary
> databases with a lot of data. I have noticed that when these applications
> run, they seem to starve the system of RAM. This is indicated by an
> increase in the RAM used by the SQL process and a heavy amount of disk
I/O.
> My assumption is that when the temp db's are created, SQL tries to get as
> much RAM from the OS as possible which creates swapping non-SQL memory to
> the pagefile and SQL internally needs to free RAM and thus needs to write
> cached data out to disk.
> Questions:
> 1) Are my assumptions correct in the usage of the temp databases?
> 2) Are there any workarounds to this?
> 3) Since SQL optimizes RAM over time, I would guess that no matter what
we
> do, SQL will always fill up its internal RAM with cached data, thus adding
> more RAM to the system won't resolve these symptoms, it will merely delay
> them by taking more time to cache all the RAM. (Our applications are
> already filling up SQL with 2 GB of RAM. I suspect moving to 4GB will not
> have a significant improvement due to the memory address extensions. 64
bit
> SQL would probably be our only solution to flood the system with enough
RAM
> that our apps can't fill up SQL's internal RAM usage, but 64 bit SQL and
> enough RAM in a system for this would be significanlty expensive for our
> needs.)
>
> Thank You,
>
|||Yes I was referring to creating databases then dropping them. In one
particular application, a lot of the stored procedures are running
algorithms that do this.
Thank You for the feedback. Now that we have a monitoring system in place,
I will experiment with restricting SQL to 1.5 GB or so and monitor memory
swapping and related disk I/O.
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:OzqSg7oIEHA.3276@.TK2MSFTNGP09.phx.gbl...
> Kevin,
> What do you mean by Temp databases? Are you creating databases and then
> dropping them? Or do you mean temporary tables being created in the
tempdb
> database? If you only have 2GB of ram and your using it all I would
> suggest you set your max memory setting in sql server to around 1.5GB to
> allow the OS and memtoleave areas some slack. If you are memory
constrained
> and it sounds like you are, then going to 4GB can make a huge difference.
> You can set the /3gb switch to allow sql server use of up to 3GB and leave
> 1GB for the OS. This should alleviate most of the OS paging and sql
server[vbcol=seagreen]
> can use it's caching mechanisims as designed without dealing with the OS
> swap file.
> --
> Andrew J. Kelly SQL MVP
>
> "Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
> news:c5jhcm$42i$1@.grandcanyon.binc.net...
applications[vbcol=seagreen]
> I/O.
as[vbcol=seagreen]
to[vbcol=seagreen]
write[vbcol=seagreen]
> we
adding[vbcol=seagreen]
delay[vbcol=seagreen]
not
> bit
> RAM
>
databases with a lot of data. I have noticed that when these applications
run, they seem to starve the system of RAM. This is indicated by an
increase in the RAM used by the SQL process and a heavy amount of disk I/O.
My assumption is that when the temp db's are created, SQL tries to get as
much RAM from the OS as possible which creates swapping non-SQL memory to
the pagefile and SQL internally needs to free RAM and thus needs to write
cached data out to disk.
Questions:
1) Are my assumptions correct in the usage of the temp databases?
2) Are there any workarounds to this?
3) Since SQL optimizes RAM over time, I would guess that no matter what we
do, SQL will always fill up its internal RAM with cached data, thus adding
more RAM to the system won't resolve these symptoms, it will merely delay
them by taking more time to cache all the RAM. (Our applications are
already filling up SQL with 2 GB of RAM. I suspect moving to 4GB will not
have a significant improvement due to the memory address extensions. 64 bit
SQL would probably be our only solution to flood the system with enough RAM
that our apps can't fill up SQL's internal RAM usage, but 64 bit SQL and
enough RAM in a system for this would be significanlty expensive for our
needs.)
Thank You,
Kevin,
What do you mean by Temp databases? Are you creating databases and then
dropping them? Or do you mean temporary tables being created in the tempdb
database? If you only have 2GB of ram and your using it all I would
suggest you set your max memory setting in sql server to around 1.5GB to
allow the OS and memtoleave areas some slack. If you are memory constrained
and it sounds like you are, then going to 4GB can make a huge difference.
You can set the /3gb switch to allow sql server use of up to 3GB and leave
1GB for the OS. This should alleviate most of the OS paging and sql server
can use it's caching mechanisims as designed without dealing with the OS
swap file.
Andrew J. Kelly SQL MVP
"Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
news:c5jhcm$42i$1@.grandcanyon.binc.net...
> We have some internal applications that rely heavily on using temporary
> databases with a lot of data. I have noticed that when these applications
> run, they seem to starve the system of RAM. This is indicated by an
> increase in the RAM used by the SQL process and a heavy amount of disk
I/O.
> My assumption is that when the temp db's are created, SQL tries to get as
> much RAM from the OS as possible which creates swapping non-SQL memory to
> the pagefile and SQL internally needs to free RAM and thus needs to write
> cached data out to disk.
> Questions:
> 1) Are my assumptions correct in the usage of the temp databases?
> 2) Are there any workarounds to this?
> 3) Since SQL optimizes RAM over time, I would guess that no matter what
we
> do, SQL will always fill up its internal RAM with cached data, thus adding
> more RAM to the system won't resolve these symptoms, it will merely delay
> them by taking more time to cache all the RAM. (Our applications are
> already filling up SQL with 2 GB of RAM. I suspect moving to 4GB will not
> have a significant improvement due to the memory address extensions. 64
bit
> SQL would probably be our only solution to flood the system with enough
RAM
> that our apps can't fill up SQL's internal RAM usage, but 64 bit SQL and
> enough RAM in a system for this would be significanlty expensive for our
> needs.)
>
> Thank You,
>
|||Yes I was referring to creating databases then dropping them. In one
particular application, a lot of the stored procedures are running
algorithms that do this.
Thank You for the feedback. Now that we have a monitoring system in place,
I will experiment with restricting SQL to 1.5 GB or so and monitor memory
swapping and related disk I/O.
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:OzqSg7oIEHA.3276@.TK2MSFTNGP09.phx.gbl...
> Kevin,
> What do you mean by Temp databases? Are you creating databases and then
> dropping them? Or do you mean temporary tables being created in the
tempdb
> database? If you only have 2GB of ram and your using it all I would
> suggest you set your max memory setting in sql server to around 1.5GB to
> allow the OS and memtoleave areas some slack. If you are memory
constrained
> and it sounds like you are, then going to 4GB can make a huge difference.
> You can set the /3gb switch to allow sql server use of up to 3GB and leave
> 1GB for the OS. This should alleviate most of the OS paging and sql
server[vbcol=seagreen]
> can use it's caching mechanisims as designed without dealing with the OS
> swap file.
> --
> Andrew J. Kelly SQL MVP
>
> "Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
> news:c5jhcm$42i$1@.grandcanyon.binc.net...
applications[vbcol=seagreen]
> I/O.
as[vbcol=seagreen]
to[vbcol=seagreen]
write[vbcol=seagreen]
> we
adding[vbcol=seagreen]
delay[vbcol=seagreen]
not
> bit
> RAM
>
Friday, March 9, 2012
Performance of SQL
I am using Reporting Services and I have a heavily parameterised report which
update each others lists etc.
When I run the report through the web service, the sql; server goes
ballistic, and runs at 100% for several minutes.
I have looked in SQL Profiler at what is going on and the actual queries (my
SQL) is only taking a few seconds to run, but there is loads of other stuff
happening, moving around chunkdata and the like. Also looking at the current
activity, I can see
several Shared DB Locks on my database and it is all looking pretty nasty.
My reports are all dynamic so is there anyway that I can strip back all of
these other things. I am quite prepared to belive that there is a problem in
my code, but I could translate the entire database into French quicker than
my 3 second SQL runs.
Can anyone shed any light on this."Tom Robson" wrote:
> I am using Reporting Services and I have a heavily parameterised report which
> update each others lists etc.
> When I run the report through the web service, the sql; server goes
> ballistic, and runs at 100% for several minutes.
> I have looked in SQL Profiler at what is going on and the actual queries (my
> SQL) is only taking a few seconds to run, but there is loads of other stuff
> happening, moving around chunkdata and the like. Also looking at the current
> activity, I can see
> several Shared DB Locks on my database and it is all looking pretty nasty.
> My reports are all dynamic so is there anyway that I can strip back all of
> these other things. I am quite prepared to belive that there is a problem in
> my code, but I could translate the entire database into French quicker than
> my 3 second SQL runs.
> Can anyone shed any light on this.|||Tom,
Try modifying your queries for using Stored Procedures. I am really not
aware how you are using the parameters or else prepare your data's in the
form of table or views so that you can run the reporting services on the view
or tables. if you are using 2005 prepare a report model and use it.
Regards
Amarnath
"Tom Robson" wrote:
> I am using Reporting Services and I have a heavily parameterised report which
> update each others lists etc.
> When I run the report through the web service, the sql; server goes
> ballistic, and runs at 100% for several minutes.
> I have looked in SQL Profiler at what is going on and the actual queries (my
> SQL) is only taking a few seconds to run, but there is loads of other stuff
> happening, moving around chunkdata and the like. Also looking at the current
> activity, I can see
> several Shared DB Locks on my database and it is all looking pretty nasty.
> My reports are all dynamic so is there anyway that I can strip back all of
> these other things. I am quite prepared to belive that there is a problem in
> my code, but I could translate the entire database into French quicker than
> my 3 second SQL runs.
> Can anyone shed any light on this.|||Thanks for the pointers, but I am confident that this issue is a little more
than that. I have about 40 reports, some of which are very straightforward
and some that arent.
All use a few parameters, and search through a chunk of data. It is the
difference between the speed of the SQL in Query Analyser and that in Report
Designer. I cant see how precomiled SQL ios going to make a significant
difference, and I cant do what I want to do with views as the query has these
parameters and performance would go down to several minutes if I did it that
way.|||You need to clean up the database, that would speed everything up. ^_^|||Make sure you are only returning the rows you need in your report ( no
filtering.)
Make sure you are not doing extra sorting in Groups, etc, that might already
be done in the SQL.
Take a look at the log in REporting Services, and it will tell you how much
time is spent doing each of three steps... rendering often can take quite a
while... All of the data has to be loaded into memory on the IIS server, then
aggregations etc are done there..
The best thing you can do otherwise is to begin to simplify a slow report...
reducing the number of rows, sorts, groups, filters etc until you can find
the culprit..
Also try to rending using different things html, pdf. etc and see if that
makes a difference..
Also try rendering from a snapshot and see..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Tom Robson" wrote:
> I am using Reporting Services and I have a heavily parameterised report which
> update each others lists etc.
> When I run the report through the web service, the sql; server goes
> ballistic, and runs at 100% for several minutes.
> I have looked in SQL Profiler at what is going on and the actual queries (my
> SQL) is only taking a few seconds to run, but there is loads of other stuff
> happening, moving around chunkdata and the like. Also looking at the current
> activity, I can see
> several Shared DB Locks on my database and it is all looking pretty nasty.
> My reports are all dynamic so is there anyway that I can strip back all of
> these other things. I am quite prepared to belive that there is a problem in
> my code, but I could translate the entire database into French quicker than
> my 3 second SQL runs.
> Can anyone shed any light on this.|||Thank you all fo0r your advice,
Except Sorcerdon. What makes you think you know anything about my database?
Anyway. I will have a look at the reports, maybe reinstall a few things and
see if it clears up the mess. It certainly appears that it relates to
something between my SQL and hitting nthe database. I know that my SQL is
fine, as I can run that against the db and it is fine. Does Report Server
dump the results off somewhere before rendering? Or is it straight back via
asp.net data providers.?
update each others lists etc.
When I run the report through the web service, the sql; server goes
ballistic, and runs at 100% for several minutes.
I have looked in SQL Profiler at what is going on and the actual queries (my
SQL) is only taking a few seconds to run, but there is loads of other stuff
happening, moving around chunkdata and the like. Also looking at the current
activity, I can see
several Shared DB Locks on my database and it is all looking pretty nasty.
My reports are all dynamic so is there anyway that I can strip back all of
these other things. I am quite prepared to belive that there is a problem in
my code, but I could translate the entire database into French quicker than
my 3 second SQL runs.
Can anyone shed any light on this."Tom Robson" wrote:
> I am using Reporting Services and I have a heavily parameterised report which
> update each others lists etc.
> When I run the report through the web service, the sql; server goes
> ballistic, and runs at 100% for several minutes.
> I have looked in SQL Profiler at what is going on and the actual queries (my
> SQL) is only taking a few seconds to run, but there is loads of other stuff
> happening, moving around chunkdata and the like. Also looking at the current
> activity, I can see
> several Shared DB Locks on my database and it is all looking pretty nasty.
> My reports are all dynamic so is there anyway that I can strip back all of
> these other things. I am quite prepared to belive that there is a problem in
> my code, but I could translate the entire database into French quicker than
> my 3 second SQL runs.
> Can anyone shed any light on this.|||Tom,
Try modifying your queries for using Stored Procedures. I am really not
aware how you are using the parameters or else prepare your data's in the
form of table or views so that you can run the reporting services on the view
or tables. if you are using 2005 prepare a report model and use it.
Regards
Amarnath
"Tom Robson" wrote:
> I am using Reporting Services and I have a heavily parameterised report which
> update each others lists etc.
> When I run the report through the web service, the sql; server goes
> ballistic, and runs at 100% for several minutes.
> I have looked in SQL Profiler at what is going on and the actual queries (my
> SQL) is only taking a few seconds to run, but there is loads of other stuff
> happening, moving around chunkdata and the like. Also looking at the current
> activity, I can see
> several Shared DB Locks on my database and it is all looking pretty nasty.
> My reports are all dynamic so is there anyway that I can strip back all of
> these other things. I am quite prepared to belive that there is a problem in
> my code, but I could translate the entire database into French quicker than
> my 3 second SQL runs.
> Can anyone shed any light on this.|||Thanks for the pointers, but I am confident that this issue is a little more
than that. I have about 40 reports, some of which are very straightforward
and some that arent.
All use a few parameters, and search through a chunk of data. It is the
difference between the speed of the SQL in Query Analyser and that in Report
Designer. I cant see how precomiled SQL ios going to make a significant
difference, and I cant do what I want to do with views as the query has these
parameters and performance would go down to several minutes if I did it that
way.|||You need to clean up the database, that would speed everything up. ^_^|||Make sure you are only returning the rows you need in your report ( no
filtering.)
Make sure you are not doing extra sorting in Groups, etc, that might already
be done in the SQL.
Take a look at the log in REporting Services, and it will tell you how much
time is spent doing each of three steps... rendering often can take quite a
while... All of the data has to be loaded into memory on the IIS server, then
aggregations etc are done there..
The best thing you can do otherwise is to begin to simplify a slow report...
reducing the number of rows, sorts, groups, filters etc until you can find
the culprit..
Also try to rending using different things html, pdf. etc and see if that
makes a difference..
Also try rendering from a snapshot and see..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Tom Robson" wrote:
> I am using Reporting Services and I have a heavily parameterised report which
> update each others lists etc.
> When I run the report through the web service, the sql; server goes
> ballistic, and runs at 100% for several minutes.
> I have looked in SQL Profiler at what is going on and the actual queries (my
> SQL) is only taking a few seconds to run, but there is loads of other stuff
> happening, moving around chunkdata and the like. Also looking at the current
> activity, I can see
> several Shared DB Locks on my database and it is all looking pretty nasty.
> My reports are all dynamic so is there anyway that I can strip back all of
> these other things. I am quite prepared to belive that there is a problem in
> my code, but I could translate the entire database into French quicker than
> my 3 second SQL runs.
> Can anyone shed any light on this.|||Thank you all fo0r your advice,
Except Sorcerdon. What makes you think you know anything about my database?
Anyway. I will have a look at the reports, maybe reinstall a few things and
see if it clears up the mess. It certainly appears that it relates to
something between my SQL and hitting nthe database. I know that my SQL is
fine, as I can run that against the db and it is fine. Does Report Server
dump the results off somewhere before rendering? Or is it straight back via
asp.net data providers.?
Subscribe to:
Posts (Atom)