Poor Performing Query

Here is the situation: I have a process that auto-generates reports and e-mails them to end users (no, we are not allowed to use SQL Mail due to security issues and the fact that we are a Lotus shop). The process runs the query, saves the data to a text file and then mails the file as an attachment.

If the particular report to be run has multiple recipients, we make multiple passes (ie, we run the same query multiple times and send each recipient a "personalized" version of the report).

The issue is that one of my auto reports fails for just the first recipient. Subsequent recipients all receive the report normally, but the first recipient consistently fails to receive the data.

When I run the T-SQL multiple times in Query analyzer, I get the following results:

Pass 1: 87 seconds
Pass 2: 2 seconds
Pass 3: 3 seconds

I know that the process that we wrote (which is wrapped in a DLL) uses an ADO connection/command timeout setting of 60 seconds (both properties are set to the same value).

The SQL is not a stored proc. When I run the Index tuning wizard, no additional indexes are recommended.

I'm struggling to determine the next step.

1. I am loathe to increase the command timeout setting, since I have already done that once (from 30 seconds to one minute). I'm pretty sure this will only defer final resolution.

2. I suppose I could create a stored proc (so that it doesn't have to recompile the execution plan each time), but that would make this one report different from the hundreds of other reports that my super users have created; it would also mean that my super users would not be able to control the layout of the report without coming to me.

3. I have a feeling I should be using query hints or some such, but I am completely unfamiliar with these optimization tools.

Is there another approach that I am overlooking?

Regards,

Hugh ScottIf these are similar then I would guess that for the first call it has to read the data from disk. For subsequent calls the data is in memory so is much quicker.

Maybe put in a dummy call first?|||Yes, the calls are identical. I agree with you that on subsequent passes, the data is being read from memory. What has me puzzled is what I can do about it. I have considered putting in a dummy call first (as you suggested), but I wanted to try something a little more orthodox first.

Do you think that adding memory to the server would help? The server currently has 2.6 GB of memory. The database itself is a little over 30 GB now. I have run performance monitor on the server. While pages/sec does spike during this operation, it usually averages around 4.5. It does not seem sufficiently spiky to warrant $4,000 for additional memory.

Some more information:

There are three tables involved:

table a left join view b left join table c

Table a: 5.4 million rows (~ 4 GB)
View b: 1.3 million rows (~ 2.3 GB)
View c: 15 rows

View b uses the UNION operator and thus cannot be indexed.
Table a is indexed six ways from Sunday.

Thanks again,

Hugh Scott

Originally posted by nigelrivett
If these are similar then I would guess that for the first call it has to read the data from disk. For subsequent calls the data is in memory so is much quicker.

Maybe put in a dummy call first?|||What's your UNION view code? UNION queries are very handy, and I've used them many times myself, but just as often I've seen them used unnecessarily. Perhaps there is a more efficient method of combining your view code directly in your procedure code.

blindman|||UNION views can be indexed. Also, have you tried to see what you get on IO stats ON when doing a SELECT from just that view using WHERE that would match your JOIN?|||Views can be indexed, but unless you use a clustered index then the index must be recreated each time the view is called, and you lose the benefit of indexes on the underlying tables.

Clustered indexes on views can greatly boost performance, but they cannot be used on UNION queries. From books-online:

A view must meet these requirements before you can create a clustered index on it:
.
.
.
The SELECT statement in the view cannot contain these Transact-SQL syntax elements:
.
.
.UNION operator.

blindman|||blindman: thanks for the reminder. UNION is not allowed in indexed views. But you're also referring to non-clustered indexes. I haven't found any info that would suggest that a non-clustered index on a view needs to be rebuilt each time the view is called. Can you enlighten me here?

hmscott: adding memory will not resolve 87-second processing time on the first call. Adding indexes to base tables may.|||Normally the results of a view are not stored in the database, but if a clustered index is created on the view then the results are stored just like a table, and the values in the view are updated whenever the values in the underlying table are updated. (This of course adds overhead to processing changes on the underlying tables.) The clustered index is necessary in order to update the values on the view.

Long story short, if you don't use a clustered index then the results are not stored and thus any indexing must be recreated each time.

Honestly, I don't know whether non-clustered indexes are maintained on views that also have clustered indexes. I think I also read somewhere that you can't create an index on a view unless it also has a clustered index, so maybe UNION queries can't have indexes at all.

It's also possible that the optimizer might be smart enough to apply filters from the procedure to the underlying tables prior to creating the UNION view when it is called, but this probably depends on a lot of factors.

I've just seen too many instances when a UNION view was used instead of a more appropriate WHERE clause criteria.

blindmansql

Poor performance on SQL 2000 Clustered

Hi all,
i have a 2-node cluster with SQL 2000. I've received many complaints
about the performance. My users tell me that the server is very slow.
The boxes are new (2 cPu Intel Xeon 2'8 GHZ and 4 Gb RAM, ...)
I have used PerfMon to see tipical counters and have seen about 100
page error/sec in memory counter.
In task manager i can see CPU about 4% CPU time and the system has
about 1'5 Gb RAM free.
How can i increase the performance? How can I review my cluster
configuration to get a better situation? I'm new in clustering and i
want to know how can i do it. URLs, all...
Thanks for your help
Clustering has nothing to do with performance of SQL Server. It is a
hardware fail over technology only. You need to do some investigation to see
what the issues are. These should get you started:
http://www.sql-server-performance.co...ce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pro...perations.mspx
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
<salva.gomez@.gmail.com> wrote in message
news:1159529059.960231.275970@.m73g2000cwd.googlegr oups.com...
> Hi all,
> i have a 2-node cluster with SQL 2000. I've received many complaints
> about the performance. My users tell me that the server is very slow.
> The boxes are new (2 cPu Intel Xeon 2'8 GHZ and 4 Gb RAM, ...)
> I have used PerfMon to see tipical counters and have seen about 100
> page error/sec in memory counter.
> In task manager i can see CPU about 4% CPU time and the system has
> about 1'5 Gb RAM free.
> How can i increase the performance? How can I review my cluster
> configuration to get a better situation? I'm new in clustering and i
> want to know how can i do it. URLs, all...
> Thanks for your help
>
sql

Poor performance - Large memory consumption by app.

I have a SQL Server 2003sp3 running on a Windows 2003 server. The
users are experiencing performance problems on their apps that seem to
be related to large memory consumption in the sqlserver process.
We have two instances of SQL running. One running the old accounting
system, one running the new accounting software. Both are vertical
market proprietary apps, and I don't have much access the the inner
workings.
The problems seem to start when the service running the new app
starts to consume a lot of memory. While the old app will climb to
about 600Mg and stay there (read from Task Man), the new app will climb
to over 1.7 Gig. That's when things start to crawl. I reboot and
things return to normal, but the new app's memory usage continues to
creep up.
The software has been installed since February, but this just started
happening a few weeks ago. I'm not sure what could be causing this
(other than problems with the software itself, I've asked their tech
support about it, but haven't heard much back).
Everything else seems normal (all performance monitors are nominal).
The only other thing I've noticed that's strange are some errors in
SQLDIAG.txt that state: "This database optimized for 8 processes , this
has been exceeded by 2" I understand this is an error related to MSDE,
but I'm not running MSDE and have never run it on this machine. It's
always been SQLServer 2000.
I'm not really well educated on SQL Server, so I'm not sure where to
turn next. Any advice would be apreciated.
(The two apps in question are "Wind 2" and the problem child
"Vision", both AEC industry accouting/project management apps).
Thanks
JIM HELFER | COMPUTER SYSTEMS ADMINISTRATOR | 412-321-0551 x330 |
JAH222@.WTWARCH.COM
WTW ARCHITECTS | TIMBER COURT | 127 ANDERSON STREET | PITTSBURGH, PA 15212Read about sp_configure ands the "max server memory" setting. Also, you do have MSDE or Personal
Edition, else you wouldn't get that warning. These editions had a performance throttling mechanism
when > 8 concurrently executing queries.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jim Helfer" <jhelfer@.wtwarch.com> wrote in message news:Ov7eVpVmHHA.4872@.TK2MSFTNGP03.phx.gbl...
> I have a SQL Server 2003sp3 running on a Windows 2003 server. The users are experiencing
> performance problems on their apps that seem to be related to large memory consumption in the
> sqlserver process.
> We have two instances of SQL running. One running the old accounting system, one running the new
> accounting software. Both are vertical market proprietary apps, and I don't have much access the
> the inner workings.
> The problems seem to start when the service running the new app starts to consume a lot of
> memory. While the old app will climb to about 600Mg and stay there (read from Task Man), the new
> app will climb to over 1.7 Gig. That's when things start to crawl. I reboot and things return to
> normal, but the new app's memory usage continues to creep up.
> The software has been installed since February, but this just started happening a few weeks ago.
> I'm not sure what could be causing this (other than problems with the software itself, I've asked
> their tech support about it, but haven't heard much back).
> Everything else seems normal (all performance monitors are nominal). The only other thing I've
> noticed that's strange are some errors in SQLDIAG.txt that state: "This database optimized for 8
> processes , this has been exceeded by 2" I understand this is an error related to MSDE, but I'm
> not running MSDE and have never run it on this machine. It's always been SQLServer 2000.
> I'm not really well educated on SQL Server, so I'm not sure where to turn next. Any advice would
> be apreciated.
> (The two apps in question are "Wind 2" and the problem child "Vision", both AEC industry
> accouting/project management apps).
> Thanks
> JIM HELFER | COMPUTER SYSTEMS ADMINISTRATOR | 412-321-0551 x330 | JAH222@.WTWARCH.COM
> WTW ARCHITECTS | TIMBER COURT | 127 ANDERSON STREET | PITTSBURGH, PA 15212
>
>|||Tibor Karaszi wrote:
> Read about sp_configure ands the "max server memory" setting. Also, you
> do have MSDE or Personal Edition, else you wouldn't get that warning.
> These editions had a performance throttling mechanism when > 8
> concurrently executing queries.
>
Huh. You're right, I have SQL Server Desktop Engine installed. No idea
why. It's an Proliant box, so maybe Compaq Insight Manager installed it.
With Max server memory, are you telling me to set this to limit the
amount of Ram this process uses? or to let it use it all? There are 4
Gig in the machine, and this is the most important program on this
machine, so I want to give it as many resources as I can.
Thanks
JIM HELFER | SYSTEMS ADMINISTRATOR
WTW ARCHITECTS|||In your earlier post, you talk about "the old app" and "the new app", one stayed at 600MB and when
the other grew up to 1.7GB things got slow. Perhaps cap the "big" one at 1.5 GB? Or so...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jim Helfer" <jhelfer@.wtwarch.com> wrote in message news:OYSU5FWmHHA.3704@.TK2MSFTNGP02.phx.gbl...
> Tibor Karaszi wrote:
>> Read about sp_configure ands the "max server memory" setting. Also, you do have MSDE or Personal
>> Edition, else you wouldn't get that warning. These editions had a performance throttling
>> mechanism when > 8 concurrently executing queries.
> Huh. You're right, I have SQL Server Desktop Engine installed. No idea why. It's an Proliant
> box, so maybe Compaq Insight Manager installed it.
> With Max server memory, are you telling me to set this to limit the amount of Ram this process
> uses? or to let it use it all? There are 4 Gig in the machine, and this is the most important
> program on this machine, so I want to give it as many resources as I can.
> Thanks
> JIM HELFER | SYSTEMS ADMINISTRATOR
> WTW ARCHITECTS|||Tibor Karaszi wrote:
> In your earlier post, you talk about "the old app" and "the new app",
> one stayed at 600MB and when the other grew up to 1.7GB things got slow.
> Perhaps cap the "big" one at 1.5 GB? Or so...
>
OK, I'll look into it. Thanks.
Jim Helfer

plz-help enumeration database objects that users roles have ?

Hello
I I just inhertied a database that I am trying to document.
I have many user roles and users under this database. and I want to put in
the documentation the user roles and the tables and object they have access
to them. as well as the users under the roles.
ofcourse there are a lot of data and collecting it manually will take ages
so I am wondering if smbdy has any suggestion or some sql code that hits the
system tables ? I have never touched any system tables or DB and I have no
map or definition for them so I am pretty relunctant on diving in it ?
SimoNot sure what information you are exactly looking for, but you can get most
information from sp_helprotect, sp_helpuser & sp_helprole system procedures.
Please refer to SQL Server Books Online for more information on these
procedures.
Anith|||well I just want a listing of user roles and their security rights on DB
objects.
I am looking at the stored procs now but if there is soemthing already done
outhere then it will save me some work.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uYCCFJb5DHA.2404@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Not sure what information you are exactly looking for, but you can get

---

most

quote:

---

> information from sp_helprotect, sp_helpuser & sp_helprole system

---

procedures.

quote:

---

> Please refer to SQL Server Books Online for more information on these
> procedures.
> --
> Anith
>

---

|||other two has has good recommendation and that is very root of the
resolution. Not much more time you can save. If this is just for
information, other recommendations can get you that information in
5-10 mins. If this is more than just information gathering, you can
use PowerPoint, Access, Visio, SQL Diagram, and host of other
applications.

plz-help enumeration database objects that users roles have ?

Hello
I I just inhertied a database that I am trying to document.
I have many user roles and users under this database. and I want to put in
the documentation the user roles and the tables and object they have access
to them. as well as the users under the roles.
ofcourse there are a lot of data and collecting it manually will take ages
so I am wondering if smbdy has any suggestion or some sql code that hits the
system tables ? I have never touched any system tables or DB and I have no
map or definition for them so I am pretty relunctant on diving in it ?
SimoNot sure what information you are exactly looking for, but you can get most
information from sp_helprotect, sp_helpuser & sp_helprole system procedures.
Please refer to SQL Server Books Online for more information on these
procedures.
--
Anith|||well I just want a listing of user roles and their security rights on DB
objects.
I am looking at the stored procs now but if there is soemthing already done
outhere then it will save me some work.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uYCCFJb5DHA.2404@.TK2MSFTNGP11.phx.gbl...
> Not sure what information you are exactly looking for, but you can get
most
> information from sp_helprotect, sp_helpuser & sp_helprole system
procedures.
> Please refer to SQL Server Books Online for more information on these
> procedures.
> --
> Anith
>|||other two has has good recommendation and that is very root of the
resolution. Not much more time you can save. If this is just for
information, other recommendations can get you that information in
5-10 mins. If this is more than just information gathering, you can
use PowerPoint, Access, Visio, SQL Diagram, and host of other
applications.sql

Please post setup and installation questions in the Setup forum

This forum is intended for users who are new to SQL Server, and have basic usage questions. If you have setup or installation issues or questions, you should check out the Setup forum.

Thanks

There was also a feature in Visual Studio 2003 whereby we could view and manage the database present on the server(s) [I think it was called the SQL Enterprise Manager], and also a tool called the Query Analyzer where we could run queries on the databases. Are these also scrapped in VS 2005? What are the alternatives if that is the case?|||

How do I upgrade a SQL Server 2005 trial version with an expired license? I have a real license. If I install a new version, I don't want to lose the reports I created from the old version.

Thank you.

|||

Check out this article. If it's not already too late.

http://support.microsoft.com/?kbid=914158

|||Where is the "Setup Forum"?|||You can find the Setup forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=95&SiteID=1 |||

Please help me to solve.

How to rectify following error

in Ms Access 2003 in windows20000

(It is working in XP)

"Microsoft Office Access can't open the database because it is missing,or opened exclusively by another user ".

|||When I run my setup.exe and get to the window for "components to install" the Reporting Services option is greyed out! Any ideas? The only thing I can think of is that the person who gave me the software (he isn't a DBA, and don't even ask why I have to go through him!!) gave me the wrong files? or downloaded some partial CD? I'm lost!|||It means you do not have one of the pre-req's, almost certainly IIS.

Please post setup and installation questions in the Setup forum

This forum is intended for users who are new to SQL Server, and have basic usage questions. If you have setup or installation issues or questions, you should check out the Setup forum.

Thanks

There was also a feature in Visual Studio 2003 whereby we could view and manage the database present on the server(s) [I think it was called the SQL Enterprise Manager], and also a tool called the Query Analyzer where we could run queries on the databases. Are these also scrapped in VS 2005? What are the alternatives if that is the case?|||

How do I upgrade a SQL Server 2005 trial version with an expired license? I have a real license. If I install a new version, I don't want to lose the reports I created from the old version.

Thank you.

|||

Check out this article. If it's not already too late.

http://support.microsoft.com/?kbid=914158

|||Where is the "Setup Forum"?
|||You can find the Setup forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=95&SiteID=1 |||

Please help me to solve.

How to rectify following error

in Ms Access 2003 in windows20000

(It is working in XP)

"Microsoft Office Access can't open the database because it is missing,or opened exclusively by another user ".

|||When I run my setup.exe and get to the window for "components to install" the Reporting Services option is greyed out! Any ideas? The only thing I can think of is that the person who gave me the software (he isn't a DBA, and don't even ask why I have to go through him!!) gave me the wrong files? or downloaded some partial CD? I'm lost!|||It means you do not have one of the pre-req's, almost certainly IIS.

Please post setup and installation questions in the Setup forum

This forum is intended for users who are new to SQL Server, and have basic usage questions. If you have setup or installation issues or questions, you should check out the Setup forum.

Thanks

There was also a feature in Visual Studio 2003 whereby we could view and manage the database present on the server(s) [I think it was called the SQL Enterprise Manager], and also a tool called the Query Analyzer where we could run queries on the databases. Are these also scrapped in VS 2005? What are the alternatives if that is the case?|||

How do I upgrade a SQL Server 2005 trial version with an expired license? I have a real license. If I install a new version, I don't want to lose the reports I created from the old version.

Thank you.

|||

Check out this article. If it's not already too late.

http://support.microsoft.com/?kbid=914158

|||Where is the "Setup Forum"?|||You can find the Setup forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=95&SiteID=1 |||

Please help me to solve.

How to rectify following error

in Ms Access 2003 in windows20000

(It is working in XP)

"Microsoft Office Access can't open the database because it is missing,or opened exclusively by another user ".

|||When I run my setup.exe and get to the window for "components to install" the Reporting Services option is greyed out! Any ideas? The only thing I can think of is that the person who gave me the software (he isn't a DBA, and don't even ask why I have to go through him!!) gave me the wrong files? or downloaded some partial CD? I'm lost!|||It means you do not have one of the pre-req's, almost certainly IIS.

Please post setup and installation questions in the Setup forum

This forum is intended for users who are new to SQL Server, and have basic usage questions. If you have setup or installation issues or questions, you should check out the Setup forum.

Thanks

There was also a feature in Visual Studio 2003 whereby we could view and manage the database present on the server(s) [I think it was called the SQL Enterprise Manager], and also a tool called the Query Analyzer where we could run queries on the databases. Are these also scrapped in VS 2005? What are the alternatives if that is the case?|||

How do I upgrade a SQL Server 2005 trial version with an expired license? I have a real license. If I install a new version, I don't want to lose the reports I created from the old version.

Thank you.

|||

Check out this article. If it's not already too late.

http://support.microsoft.com/?kbid=914158

|||Where is the "Setup Forum"?|||You can find the Setup forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=95&SiteID=1 |||

Please help me to solve.

How to rectify following error

in Ms Access 2003 in windows20000

(It is working in XP)

"Microsoft Office Access can't open the database because it is missing,or opened exclusively by another user ".

|||When I run my setup.exe and get to the window for "components to install" the Reporting Services option is greyed out! Any ideas? The only thing I can think of is that the person who gave me the software (he isn't a DBA, and don't even ask why I have to go through him!!) gave me the wrong files? or downloaded some partial CD? I'm lost!|||It means you do not have one of the pre-req's, almost certainly IIS.

Please help.. can't find how to administer userr accounts

I'm trying to administer permissions for other users so that reports can be
viewed remotely.
I'm told to go to http://myserver/reportserver but I get this error: The
permissions granted to user 'MyServer\IUSR_MyCompany' are insufficient for
performing this operation. (rsAccessDenied)
I'm told to go to http://myserver/reports and I do get a SSRS home page but
nothing on it to do anything with.
I'm told to launch SS Mgmt Studio, go into Reporting Services and
right-click on the Home folder and click Properties. I do this but then
there's nothing in there for me to work with.
Can anyone please tell me what's going on and how I can get in and
andminister user permissions?
Thanks for any help!
RonIt sounds to me like one of two things. Either you web has anonymous turned
on. If so, then all users are anonymous and no user can administer the
website.
Two, anyone who is in the local administrators group of the server can
administer RS. Are you a member of that group?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ronald S. Cook" <rcook@.westinis.com> wrote in message
news:u2JLWqaWHHA.3568@.TK2MSFTNGP06.phx.gbl...
> I'm trying to administer permissions for other users so that reports can
> be viewed remotely.
> I'm told to go to http://myserver/reportserver but I get this error: The
> permissions granted to user 'MyServer\IUSR_MyCompany' are insufficient for
> performing this operation. (rsAccessDenied)
> I'm told to go to http://myserver/reports and I do get a SSRS home page
> but nothing on it to do anything with.
> I'm told to launch SS Mgmt Studio, go into Reporting Services and
> right-click on the Home folder and click Properties. I do this but then
> there's nothing in there for me to work with.
> Can anyone please tell me what's going on and how I can get in and
> andminister user permissions?
> Thanks for any help!
> Ron
>
>
>
>|||Thanks Bruce.. I'll check those things. I appreciate the help.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23WO9KwaWHHA.388@.TK2MSFTNGP04.phx.gbl...
> It sounds to me like one of two things. Either you web has anonymous
> turned on. If so, then all users are anonymous and no user can administer
> the website.
> Two, anyone who is in the local administrators group of the server can
> administer RS. Are you a member of that group?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ronald S. Cook" <rcook@.westinis.com> wrote in message
> news:u2JLWqaWHHA.3568@.TK2MSFTNGP06.phx.gbl...
>> I'm trying to administer permissions for other users so that reports can
>> be viewed remotely.
>> I'm told to go to http://myserver/reportserver but I get this error: The
>> permissions granted to user 'MyServer\IUSR_MyCompany' are insufficient
>> for performing this operation. (rsAccessDenied)
>> I'm told to go to http://myserver/reports and I do get a SSRS home page
>> but nothing on it to do anything with.
>> I'm told to launch SS Mgmt Studio, go into Reporting Services and
>> right-click on the Home folder and click Properties. I do this but then
>> there's nothing in there for me to work with.
>> Can anyone please tell me what's going on and how I can get in and
>> andminister user permissions?
>> Thanks for any help!
>> Ron
>>
>>
>>
>>
>