a couple reporting/ normalization questions

I have one DB in specific that fails to even be in 1NF, much less 3rd. It is
used only for reporting, no OLTP. The queries are quite slow to say the
least. Every day this data is truncated and repopulated from a mainframe.
From what I can tell there are a few options here.
1; Normalize it. The problem here is there are 150+ Stored Procs dependant
on the existing structure, and of course nobody has time to re-write them.
Theres probably software that can do this for us, (?) but if so, does it
really work well? Also, the DTS Package that populates this DB every day
would need total overhauling.
2; Forget this pile of crap. Let it stay how it is for existing stuff. But
have new stuff go into Analysis Services, and then use Reporting Services.
Everyone here want to use AS and RS anyways, so thats the way we are
leaning. The problem is I have little experience with either of these tools.
Im open to the challenge and we move fairly slow anyways, so I think it can
work. Now my questions.
1; Can Reporting Services connect/report from Analysis Services?
2, Can AS be populated from a totally de-normalized DB? Can I design the
cubes how they should be, and take the data from several different tables to
populate them?
3; If #2 isnt hopefull, would I be better off to create a new, well designed
db, populate that, and from there populate AS?
All insights are appreciated.
TIA,
ChrisR
to answer number2
You should use DTS to do this
Create the structure that you think will work for you amd then you can
create your cubes (fact and dimension tables) however you want
http://sqlservercode.blogspot.com/
"ChrisR" wrote:

> I have one DB in specific that fails to even be in 1NF, much less 3rd. It is
> used only for reporting, no OLTP. The queries are quite slow to say the
> least. Every day this data is truncated and repopulated from a mainframe.
> From what I can tell there are a few options here.
> 1; Normalize it. The problem here is there are 150+ Stored Procs dependant
> on the existing structure, and of course nobody has time to re-write them.
> Theres probably software that can do this for us, (?) but if so, does it
> really work well? Also, the DTS Package that populates this DB every day
> would need total overhauling.
> 2; Forget this pile of crap. Let it stay how it is for existing stuff. But
> have new stuff go into Analysis Services, and then use Reporting Services.
> Everyone here want to use AS and RS anyways, so thats the way we are
> leaning. The problem is I have little experience with either of these tools.
> Im open to the challenge and we move fairly slow anyways, so I think it can
> work. Now my questions.
> 1; Can Reporting Services connect/report from Analysis Services?
> 2, Can AS be populated from a totally de-normalized DB? Can I design the
> cubes how they should be, and take the data from several different tables to
> populate them?
> 3; If #2 isnt hopefull, would I be better off to create a new, well designed
> db, populate that, and from there populate AS?
> All insights are appreciated.
> --
> TIA,
> ChrisR
|||Chris,
1. Yes you can use AS as a source of data to RS.
2 & 3. Depends on how the data is structured. A normalized structure to a
denormalized (star or snowflake) structure would be prefered.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:BE095EAD-5738-43EC-A2EC-1BF20E837BEF@.microsoft.com...
>I have one DB in specific that fails to even be in 1NF, much less 3rd. It
>is
> used only for reporting, no OLTP. The queries are quite slow to say the
> least. Every day this data is truncated and repopulated from a mainframe.
> From what I can tell there are a few options here.
> 1; Normalize it. The problem here is there are 150+ Stored Procs dependant
> on the existing structure, and of course nobody has time to re-write them.
> Theres probably software that can do this for us, (?) but if so, does it
> really work well? Also, the DTS Package that populates this DB every day
> would need total overhauling.
> 2; Forget this pile of crap. Let it stay how it is for existing stuff. But
> have new stuff go into Analysis Services, and then use Reporting Services.
> Everyone here want to use AS and RS anyways, so thats the way we are
> leaning. The problem is I have little experience with either of these
> tools.
> Im open to the challenge and we move fairly slow anyways, so I think it
> can
> work. Now my questions.
> 1; Can Reporting Services connect/report from Analysis Services?
> 2, Can AS be populated from a totally de-normalized DB? Can I design the
> cubes how they should be, and take the data from several different tables
> to
> populate them?
> 3; If #2 isnt hopefull, would I be better off to create a new, well
> designed
> db, populate that, and from there populate AS?
> All insights are appreciated.
> --
> TIA,
> ChrisR
|||Do both of you mean that I can, or cannot, populate well designed cubes from
my poorly designed tables? I think you are both saying I can, but want to
clarify.
Thanks, ChrisR
"ChrisR" wrote:

> I have one DB in specific that fails to even be in 1NF, much less 3rd. It is
> used only for reporting, no OLTP. The queries are quite slow to say the
> least. Every day this data is truncated and repopulated from a mainframe.
> From what I can tell there are a few options here.
> 1; Normalize it. The problem here is there are 150+ Stored Procs dependant
> on the existing structure, and of course nobody has time to re-write them.
> Theres probably software that can do this for us, (?) but if so, does it
> really work well? Also, the DTS Package that populates this DB every day
> would need total overhauling.
> 2; Forget this pile of crap. Let it stay how it is for existing stuff. But
> have new stuff go into Analysis Services, and then use Reporting Services.
> Everyone here want to use AS and RS anyways, so thats the way we are
> leaning. The problem is I have little experience with either of these tools.
> Im open to the challenge and we move fairly slow anyways, so I think it can
> work. Now my questions.
> 1; Can Reporting Services connect/report from Analysis Services?
> 2, Can AS be populated from a totally de-normalized DB? Can I design the
> cubes how they should be, and take the data from several different tables to
> populate them?
> 3; If #2 isnt hopefull, would I be better off to create a new, well designed
> db, populate that, and from there populate AS?
> All insights are appreciated.
> --
> TIA,
> ChrisR
|||Chris,
It's kinda like asking can I get 'there' from 'here' without being able to
accurately qualify where 'here' is. AS cubes are built off of denormalized
structures (star and snowflake). I'm "somewhat" sure with views and DTS you
can populate the structure tables for the AS cubes or possibly the AS cubes
themselves. Would it be easier if you were working with a normalized design
to start with? Would there be less coding and less possiblity for error?
I'd say yes to both.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1C1291EB-FAD5-41E1-8655-5E3BC7B7B695@.microsoft.com...[vbcol=seagreen]
> Do both of you mean that I can, or cannot, populate well designed cubes
> from
> my poorly designed tables? I think you are both saying I can, but want to
> clarify.
> Thanks, ChrisR
>
> "ChrisR" wrote:
sql

a couple reporting/ normalization questions

I have one DB in specific that fails to even be in 1NF, much less 3rd. It is
used only for reporting, no OLTP. The queries are quite slow to say the
least. Every day this data is truncated and repopulated from a mainframe.
From what I can tell there are a few options here.
1; Normalize it. The problem here is there are 150+ Stored Procs dependant
on the existing structure, and of course nobody has time to re-write them.
Theres probably software that can do this for us, (?) but if so, does it
really work well? Also, the DTS Package that populates this DB every day
would need total overhauling.
2; Forget this pile of crap. Let it stay how it is for existing stuff. But
have new stuff go into Analysis Services, and then use Reporting Services.
Everyone here want to use AS and RS anyways, so thats the way we are
leaning. The problem is I have little experience with either of these tools.
Im open to the challenge and we move fairly slow anyways, so I think it can
work. Now my questions.
1; Can Reporting Services connect/report from Analysis Services?
2, Can AS be populated from a totally de-normalized DB? Can I design the
cubes how they should be, and take the data from several different tables to
populate them?
3; If #2 isnt hopefull, would I be better off to create a new, well designed
db, populate that, and from there populate AS?
All insights are appreciated.
--
TIA,
ChrisRto answer number2
You should use DTS to do this
Create the structure that you think will work for you amd then you can
create your cubes (fact and dimension tables) however you want
http://sqlservercode.blogspot.com/
"ChrisR" wrote:
> I have one DB in specific that fails to even be in 1NF, much less 3rd. It is
> used only for reporting, no OLTP. The queries are quite slow to say the
> least. Every day this data is truncated and repopulated from a mainframe.
> From what I can tell there are a few options here.
> 1; Normalize it. The problem here is there are 150+ Stored Procs dependant
> on the existing structure, and of course nobody has time to re-write them.
> Theres probably software that can do this for us, (?) but if so, does it
> really work well? Also, the DTS Package that populates this DB every day
> would need total overhauling.
> 2; Forget this pile of crap. Let it stay how it is for existing stuff. But
> have new stuff go into Analysis Services, and then use Reporting Services.
> Everyone here want to use AS and RS anyways, so thats the way we are
> leaning. The problem is I have little experience with either of these tools.
> Im open to the challenge and we move fairly slow anyways, so I think it can
> work. Now my questions.
> 1; Can Reporting Services connect/report from Analysis Services?
> 2, Can AS be populated from a totally de-normalized DB? Can I design the
> cubes how they should be, and take the data from several different tables to
> populate them?
> 3; If #2 isnt hopefull, would I be better off to create a new, well designed
> db, populate that, and from there populate AS?
> All insights are appreciated.
> --
> TIA,
> ChrisR|||Chris,
1. Yes you can use AS as a source of data to RS.
2 & 3. Depends on how the data is structured. A normalized structure to a
denormalized (star or snowflake) structure would be prefered.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:BE095EAD-5738-43EC-A2EC-1BF20E837BEF@.microsoft.com...
>I have one DB in specific that fails to even be in 1NF, much less 3rd. It
>is
> used only for reporting, no OLTP. The queries are quite slow to say the
> least. Every day this data is truncated and repopulated from a mainframe.
> From what I can tell there are a few options here.
> 1; Normalize it. The problem here is there are 150+ Stored Procs dependant
> on the existing structure, and of course nobody has time to re-write them.
> Theres probably software that can do this for us, (?) but if so, does it
> really work well? Also, the DTS Package that populates this DB every day
> would need total overhauling.
> 2; Forget this pile of crap. Let it stay how it is for existing stuff. But
> have new stuff go into Analysis Services, and then use Reporting Services.
> Everyone here want to use AS and RS anyways, so thats the way we are
> leaning. The problem is I have little experience with either of these
> tools.
> Im open to the challenge and we move fairly slow anyways, so I think it
> can
> work. Now my questions.
> 1; Can Reporting Services connect/report from Analysis Services?
> 2, Can AS be populated from a totally de-normalized DB? Can I design the
> cubes how they should be, and take the data from several different tables
> to
> populate them?
> 3; If #2 isnt hopefull, would I be better off to create a new, well
> designed
> db, populate that, and from there populate AS?
> All insights are appreciated.
> --
> TIA,
> ChrisR|||Do both of you mean that I can, or cannot, populate well designed cubes from
my poorly designed tables? I think you are both saying I can, but want to
clarify.
Thanks, ChrisR
"ChrisR" wrote:
> I have one DB in specific that fails to even be in 1NF, much less 3rd. It is
> used only for reporting, no OLTP. The queries are quite slow to say the
> least. Every day this data is truncated and repopulated from a mainframe.
> From what I can tell there are a few options here.
> 1; Normalize it. The problem here is there are 150+ Stored Procs dependant
> on the existing structure, and of course nobody has time to re-write them.
> Theres probably software that can do this for us, (?) but if so, does it
> really work well? Also, the DTS Package that populates this DB every day
> would need total overhauling.
> 2; Forget this pile of crap. Let it stay how it is for existing stuff. But
> have new stuff go into Analysis Services, and then use Reporting Services.
> Everyone here want to use AS and RS anyways, so thats the way we are
> leaning. The problem is I have little experience with either of these tools.
> Im open to the challenge and we move fairly slow anyways, so I think it can
> work. Now my questions.
> 1; Can Reporting Services connect/report from Analysis Services?
> 2, Can AS be populated from a totally de-normalized DB? Can I design the
> cubes how they should be, and take the data from several different tables to
> populate them?
> 3; If #2 isnt hopefull, would I be better off to create a new, well designed
> db, populate that, and from there populate AS?
> All insights are appreciated.
> --
> TIA,
> ChrisR|||Chris,
It's kinda like asking can I get 'there' from 'here' without being able to
accurately qualify where 'here' is. AS cubes are built off of denormalized
structures (star and snowflake). I'm "somewhat" sure with views and DTS you
can populate the structure tables for the AS cubes or possibly the AS cubes
themselves. Would it be easier if you were working with a normalized design
to start with? Would there be less coding and less possiblity for error?
I'd say yes to both.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1C1291EB-FAD5-41E1-8655-5E3BC7B7B695@.microsoft.com...
> Do both of you mean that I can, or cannot, populate well designed cubes
> from
> my poorly designed tables? I think you are both saying I can, but want to
> clarify.
> Thanks, ChrisR
>
> "ChrisR" wrote:
>> I have one DB in specific that fails to even be in 1NF, much less 3rd. It
>> is
>> used only for reporting, no OLTP. The queries are quite slow to say the
>> least. Every day this data is truncated and repopulated from a mainframe.
>> From what I can tell there are a few options here.
>> 1; Normalize it. The problem here is there are 150+ Stored Procs
>> dependant
>> on the existing structure, and of course nobody has time to re-write
>> them.
>> Theres probably software that can do this for us, (?) but if so, does it
>> really work well? Also, the DTS Package that populates this DB every day
>> would need total overhauling.
>> 2; Forget this pile of crap. Let it stay how it is for existing stuff.
>> But
>> have new stuff go into Analysis Services, and then use Reporting
>> Services.
>> Everyone here want to use AS and RS anyways, so thats the way we are
>> leaning. The problem is I have little experience with either of these
>> tools.
>> Im open to the challenge and we move fairly slow anyways, so I think it
>> can
>> work. Now my questions.
>> 1; Can Reporting Services connect/report from Analysis Services?
>> 2, Can AS be populated from a totally de-normalized DB? Can I design the
>> cubes how they should be, and take the data from several different tables
>> to
>> populate them?
>> 3; If #2 isnt hopefull, would I be better off to create a new, well
>> designed
>> db, populate that, and from there populate AS?
>> All insights are appreciated.
>> --
>> TIA,
>> ChrisR

a couple reporting/ normalization questions

I have one DB in specific that fails to even be in 1NF, much less 3rd. It is
used only for reporting, no OLTP. The queries are quite slow to say the
least. Every day this data is truncated and repopulated from a mainframe.
From what I can tell there are a few options here.
1; Normalize it. The problem here is there are 150+ Stored Procs dependant
on the existing structure, and of course nobody has time to re-write them.
Theres probably software that can do this for us, (?) but if so, does it
really work well? Also, the DTS Package that populates this DB every day
would need total overhauling.
2; Forget this pile of crap. Let it stay how it is for existing stuff. But
have new stuff go into Analysis Services, and then use Reporting Services.
Everyone here want to use AS and RS anyways, so thats the way we are
leaning. The problem is I have little experience with either of these tools.
Im open to the challenge and we move fairly slow anyways, so I think it can
work. Now my questions.
1; Can Reporting Services connect/report from Analysis Services?
2, Can AS be populated from a totally de-normalized DB? Can I design the
cubes how they should be, and take the data from several different tables to
populate them?
3; If #2 isnt hopefull, would I be better off to create a new, well designed
db, populate that, and from there populate AS?
All insights are appreciated.
TIA,
ChrisRto answer number2
You should use DTS to do this
Create the structure that you think will work for you amd then you can
create your cubes (fact and dimension tables) however you want
http://sqlservercode.blogspot.com/
"ChrisR" wrote:

> I have one DB in specific that fails to even be in 1NF, much less 3rd. It
is
> used only for reporting, no OLTP. The queries are quite slow to say the
> least. Every day this data is truncated and repopulated from a mainframe.
> From what I can tell there are a few options here.
> 1; Normalize it. The problem here is there are 150+ Stored Procs dependant
> on the existing structure, and of course nobody has time to re-write them.
> Theres probably software that can do this for us, (?) but if so, does it
> really work well? Also, the DTS Package that populates this DB every day
> would need total overhauling.
> 2; Forget this pile of crap. Let it stay how it is for existing stuff. But
> have new stuff go into Analysis Services, and then use Reporting Services.
> Everyone here want to use AS and RS anyways, so thats the way we are
> leaning. The problem is I have little experience with either of these tool
s.
> Im open to the challenge and we move fairly slow anyways, so I think it ca
n
> work. Now my questions.
> 1; Can Reporting Services connect/report from Analysis Services?
> 2, Can AS be populated from a totally de-normalized DB? Can I design the
> cubes how they should be, and take the data from several different tables
to
> populate them?
> 3; If #2 isnt hopefull, would I be better off to create a new, well design
ed
> db, populate that, and from there populate AS?
> All insights are appreciated.
> --
> TIA,
> ChrisR|||Chris,
1. Yes you can use AS as a source of data to RS.
2 & 3. Depends on how the data is structured. A normalized structure to a
denormalized (star or snowflake) structure would be prefered.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:BE095EAD-5738-43EC-A2EC-1BF20E837BEF@.microsoft.com...
>I have one DB in specific that fails to even be in 1NF, much less 3rd. It
>is
> used only for reporting, no OLTP. The queries are quite slow to say the
> least. Every day this data is truncated and repopulated from a mainframe.
> From what I can tell there are a few options here.
> 1; Normalize it. The problem here is there are 150+ Stored Procs dependant
> on the existing structure, and of course nobody has time to re-write them.
> Theres probably software that can do this for us, (?) but if so, does it
> really work well? Also, the DTS Package that populates this DB every day
> would need total overhauling.
> 2; Forget this pile of crap. Let it stay how it is for existing stuff. But
> have new stuff go into Analysis Services, and then use Reporting Services.
> Everyone here want to use AS and RS anyways, so thats the way we are
> leaning. The problem is I have little experience with either of these
> tools.
> Im open to the challenge and we move fairly slow anyways, so I think it
> can
> work. Now my questions.
> 1; Can Reporting Services connect/report from Analysis Services?
> 2, Can AS be populated from a totally de-normalized DB? Can I design the
> cubes how they should be, and take the data from several different tables
> to
> populate them?
> 3; If #2 isnt hopefull, would I be better off to create a new, well
> designed
> db, populate that, and from there populate AS?
> All insights are appreciated.
> --
> TIA,
> ChrisR|||Do both of you mean that I can, or cannot, populate well designed cubes from
my poorly designed tables? I think you are both saying I can, but want to
clarify.
Thanks, ChrisR
"ChrisR" wrote:

> I have one DB in specific that fails to even be in 1NF, much less 3rd. It
is
> used only for reporting, no OLTP. The queries are quite slow to say the
> least. Every day this data is truncated and repopulated from a mainframe.
> From what I can tell there are a few options here.
> 1; Normalize it. The problem here is there are 150+ Stored Procs dependant
> on the existing structure, and of course nobody has time to re-write them.
> Theres probably software that can do this for us, (?) but if so, does it
> really work well? Also, the DTS Package that populates this DB every day
> would need total overhauling.
> 2; Forget this pile of crap. Let it stay how it is for existing stuff. But
> have new stuff go into Analysis Services, and then use Reporting Services.
> Everyone here want to use AS and RS anyways, so thats the way we are
> leaning. The problem is I have little experience with either of these tool
s.
> Im open to the challenge and we move fairly slow anyways, so I think it ca
n
> work. Now my questions.
> 1; Can Reporting Services connect/report from Analysis Services?
> 2, Can AS be populated from a totally de-normalized DB? Can I design the
> cubes how they should be, and take the data from several different tables
to
> populate them?
> 3; If #2 isnt hopefull, would I be better off to create a new, well design
ed
> db, populate that, and from there populate AS?
> All insights are appreciated.
> --
> TIA,
> ChrisR|||Chris,
It's kinda like asking can I get 'there' from 'here' without being able to
accurately qualify where 'here' is. AS cubes are built off of denormalized
structures (star and snowflake). I'm "somewhat" sure with views and DTS you
can populate the structure tables for the AS cubes or possibly the AS cubes
themselves. Would it be easier if you were working with a normalized design
to start with? Would there be less coding and less possiblity for error?
I'd say yes to both.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1C1291EB-FAD5-41E1-8655-5E3BC7B7B695@.microsoft.com...[vbcol=seagreen]
> Do both of you mean that I can, or cannot, populate well designed cubes
> from
> my poorly designed tables? I think you are both saying I can, but want to
> clarify.
> Thanks, ChrisR
>
> "ChrisR" wrote:
>

64-bit SQL Server 2005 Cluster: slow remote desktop

Hi,
We are moving from SQL Server 2000 to SQL Server 2005. Wondering if you
have the same problem or can share some of your experience to solve some
issue.
We have brand new clustered database servers which have Quad Duo CPUs
and 8GB memory each running SQL Server 2005 64bit Enterprise edition
with SP1 applied and on Win2K3 server x64 Enterprise edition. Only
database engine component are installed for now.
We have some strange behaviors from time to time that :
When we have some query running from some local PC Management Studio,
the remote desktop control is either takes a long time to connect or
very slow to react the requests. When it happened, sometime we can not
connect to the database server from Management Studio with the
connection timeout which was OK couple minutes ago. Interestingly at
those moments, the query is still running , no process blocking found,
very light CPU usage and low disk I/O detected from the monitoring tool
and it looks like the SQL server is not busy at all. No 3rd
party/identifiable process can be found running on the database server
also, the 'system idle process' is the main one with CPU count. And
overall the query just take very long than expected to finish. We could
have very normal days for a while and randomly we will encountered the
symptom above. Each time query we ran is different. Some are light and
some are heavy. The query we recently ran is to create index on many big
partitioned tables.
Thanks in advance.Hi,
Thanks for your post!
From your description, I understand that:
You installed SQL Server 2005 64bit as clustered servers. The Windows
Server 2003 was also 64bit.
When you executed the query in SQL Server Management Studio locally, the
remote desktop need a long time to connect the server and had very slow
responses for requests.
At that time, extra connections to the SQL Server were also slow and even
failed. The CPU use ratio and disk I/O level were low then.
This happened once a while. The latest query was to create index on many
large partitioned tables.
If I have misunderstood, please let me know.
To narrow down this issue, I appreciate you send me the performance report
files of SQL Server Profiler and Performance Monitor when the issue is
happening again.
Also, I appreciate you could tell me more about the query when this issue
happened:
What's kind of the query?
Does a simple query will cause that problem?
When this issue happened, what were recorded in the SQL error logs and
system event logs?
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others:
https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||American are in vacation "Independency Day".
Charles|||Hi ,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=============================================================This posting is provided "AS IS" with no warranties, and confers no rights.

64-bit SQL Server 2005 Cluster: slow remote desktop

Hi,
We are moving from SQL Server 2000 to SQL Server 2005. Wondering if you
have the same problem or can share some of your experience to solve some
issue.
We have brand new clustered database servers which have Quad Duo CPUs
and 8GB memory each running SQL Server 2005 64bit Enterprise edition
with SP1 applied and on Win2K3 server x64 Enterprise edition. Only
database engine component are installed for now.
We have some strange behaviors from time to time that :
When we have some query running from some local PC Management Studio,
the remote desktop control is either takes a long time to connect or
very slow to react the requests. When it happened, sometime we can not
connect to the database server from Management Studio with the
connection timeout which was OK couple minutes ago. Interestingly at
those moments, the query is still running , no process blocking found,
very light CPU usage and low disk I/O detected from the monitoring tool
and it looks like the SQL server is not busy at all. No 3rd
party/identifiable process can be found running on the database server
also, the 'system idle process' is the main one with CPU count. And
overall the query just take very long than expected to finish. We could
have very normal days for a while and randomly we will encountered the
symptom above. Each time query we ran is different. Some are light and
some are heavy. The query we recently ran is to create index on many big
partitioned tables.
Thanks in advance.Hi,
Thanks for your post!
From your description, I understand that:
You installed SQL Server 2005 64bit as clustered servers. The Windows
Server 2003 was also 64bit.
When you executed the query in SQL Server Management Studio locally, the
remote desktop need a long time to connect the server and had very slow
responses for requests.
At that time, extra connections to the SQL Server were also slow and even
failed. The CPU use ratio and disk I/O level were low then.
This happened once a while. The latest query was to create index on many
large partitioned tables.
If I have misunderstood, please let me know.
To narrow down this issue, I appreciate you send me the performance report
files of SQL Server Profiler and Performance Monitor when the issue is
happening again.
Also, I appreciate you could tell me more about the query when this issue
happened:
What's kind of the query?
Does a simple query will cause that problem?
When this issue happened, what were recorded in the SQL error logs and
system event logs?
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
Thanks for your post!
From your description, I understand that:
You installed SQL Server 2005 64bit as clustered servers. The Windows
Server 2003 was also 64bit.
When you executed the query in SQL Server Management Studio locally, the
remote desktop need a long time to connect the server and had very slow
responses for requests.
At that time, extra connections to the SQL Server were also slow and even
failed. The CPU use ratio and disk I/O level were low then.
This happened once a while. The latest query was to create index on many
large partitioned tables.
If I have misunderstood, please let me know.
To narrow down this issue, I appreciate you send me the performance report
files of SQL Server Profiler and Performance Monitor when the issue is
happening again.
Also, I appreciate you could tell me more about the query when this issue
happened:
What's kind of the query?
Does a simple query will cause that problem?
When this issue happened, what were recorded in the SQL error logs and
system event logs?
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||American are in vacation "Independency Day".
Charles|||Hi ,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=====================
This posting is provided "AS IS" with no warranties, and confers no rights.

6.5 reasons for slow inserts and blocking

Hallo!

I have a table in sql 6.5SP5a with 2`130`698 rows. The table has a primary
key on identity column and an insert trigger that updates the inserted row
according to some logic. Recently I've noticed that some amount of
intermittent blocking is occuring. It happens when the stored procedure that
inserts a new row in the table is receiving simultaneous calls. The
procedure looks something like this:

create procedure pr_insert_or_update
@.mode tinyint,
@.id int,
@.param1 int,
@.param2 datetime,
@.param3 varchar(3)

as

if @.mode=0
begin

insert into Table(column1, column2, column3, column4, column5)
values(param1, param2, param3, user_name(), host_name())

select @.@.identity as identity_column,
column_updated_by_the_trigger
from Table where identity_column=@.@.identity

end

else
begin

update table set column1=@.param1, column2=@.param2,
column3=@.param3, column4=user_name(), column5=host_name()
where identity_column=@.id

select @.id

end

The syprocesses waittype, status, and cmd entries for the suid at the head
of a blocking chain are usually 0x0000, runnable, and SELECT accordingly.
Although cmd is SELECT, DBCC OPENTRAN for the (user) database shows that the
oldest open transaction is "insert" and it belongs to the suid at the head
of a blocking chain. Also the exclusive blocking page locks are held.
I used the blocker script from Microsoft to gather this information. Still,
I'm stuck with finding out what is the cause of blocking since the waittype
is usually 0x0000 and DBCC PSS doesn't return any result in my case.

Any ideas on what could have caused/how to resolve blocking? Could it be
slow network when users are holding page locks for too long? Maybe I should
consider switching on "insert row lock" for the Table?

Many thanks!Two things come to mind.

1) It appears to me that you have a trigger on the table, column_updated_by_the_trigger, so the INSERT transaction is actually a couple of SQL statements linked together into 1 transaction. The INSERT plus any SQL statements within the trigger. So locks on the INSERT are being held until the trigger has been completed.

2) Once the trigger has completed, the statement will auto commit or auto rollback, I say this because I see no BEGIN TRAN, and I'm assuming the application that calls this has not issued it and you have the default settings, Implicit Transactions set to false.
So when the INSERT finishes and you have released the locks the next process that is in-line to execute the INSERT statement will happen, however the first process now will execute the SELECT, but will be locked out by the second process doing the INSERT.
Two options to solve this (a) Place a BEGIN TRAN prior to the INSERT and a COMMIT TRAN after the SELECT or (b) Place the hint (nolock) after the table name in the SELECT.

5-workload limit

Hi, does anyone has practical problem when exceeding the
5-workload limit of MSDE. Please tell me about it what
errors you get or how slow will performance degrade.
Maybe how you solve this. Tnx!
Hi Aldrin,
You won't get any errors when you exceed the 5-workload limit. When you
exceed this limit it will be logged in the SQL Server error log though. The
message in the error log will state that there were more than 8 concurrent
workloads, as 3 workloads have been reserved for the system to use.
Performance will drop of when you have more than 5 concurrent workloads, but
it will not be dramatically until you reach about 20 concurrent workloads as
far as I know. Of course it might be different on your system, so the best
thing you can do is test your own system with the maximum number of
workloads you expect to find out if you still get the performance you need.
You can find more information about the workload governor in the following
Knowledge Base article:
http://msdn.microsoft.com/library/?u...asp?frame=true
Jacco Schalkwijk
SQL Server MVP
"Aldrin" <aldrin@.wanadoo.nl> wrote in message
news:1bb5f01c42130$c45925a0$a301280a@.phx.gbl...
> Hi, does anyone has practical problem when exceeding the
> 5-workload limit of MSDE. Please tell me about it what
> errors you get or how slow will performance degrade.
> Maybe how you solve this. Tnx!
|||Thank you Jacco,
Now we're more sure to start with MSDE as backend (with
Delphi as frontend)! Not afraid anymore that everything
will stop or so. We will test this off course as you
wrote.
Greetings,
Aldrin

>--Original Message--
>Hi Aldrin,
>You won't get any errors when you exceed the 5-workload
limit. When you
>exceed this limit it will be logged in the SQL Server
error log though. The
>message in the error log will state that there were more
than 8 concurrent
>workloads, as 3 workloads have been reserved for the
system to use.
>Performance will drop of when you have more than 5
concurrent workloads, but
>it will not be dramatically until you reach about 20
concurrent workloads as
>far as I know. Of course it might be different on your
system, so the best
>thing you can do is test your own system with the
maximum number of
>workloads you expect to find out if you still get the
performance you need.
>You can find more information about the workload
governor in the following
>Knowledge Base article:
>
>http://msdn.microsoft.com/library/?url=/library/en-
us/architec/8_ar_sa2_0ciq.asp?frame=true[color=darkblue]
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Aldrin" <aldrin@.wanadoo.nl> wrote in message
>news:1bb5f01c42130$c45925a0$a301280a@.phx.gbl...
the
>
>.
>

40 Go database with 2 Go Ram?

Hi,
I got some performance problems. I have a 40 Go database on a dual core CPU
with 2 Go of RAM.
Sometime it's fast, sometime it's slow. Some request take more than 20
seconds to complete. And if I re-run them right after, it executes in a flash
even if I changed some paramaters.
For instance, I have a 10 Go table with indexes for a total of 15 Go. Other
tables where I run the most queries are 150 Mo with a 64 Mo index.
Since the performance is not always the same, should I conclude this is a
memory problem? How to know what is the right size of RAM? I can't put 40 Go
RAM or I'll go out of business...!
Any idea would be appreciated.
Thanks
StephaneThis may be a sign that you need better indexing. The slow/fast is what
happens when the server needs to read in and cache your new data, which got
flushed because it needed the space for old data. But it's hard to give any
general rule for a reasonable ratio of data to RAM. A ten gb table with 5gb
of indexes, still doesn't say whether 2gb is enough RAM. But remember, since
the OS typically takes half a gb to 1gb, going from 2gb RAM on a machine to
4gb, can almost triple your effective cache space, which is usually a good
thing!
Josh
"Stephane" wrote:
> Hi,
> I got some performance problems. I have a 40 Go database on a dual core CPU
> with 2 Go of RAM.
> Sometime it's fast, sometime it's slow. Some request take more than 20
> seconds to complete. And if I re-run them right after, it executes in a flash
> even if I changed some paramaters.
> For instance, I have a 10 Go table with indexes for a total of 15 Go. Other
> tables where I run the most queries are 150 Mo with a 64 Mo index.
> Since the performance is not always the same, should I conclude this is a
> memory problem? How to know what is the right size of RAM? I can't put 40 Go
> RAM or I'll go out of business...!
> Any idea would be appreciated.
> Thanks
> Stephane|||> Since the performance is not always the same, should I conclude this is a
> memory problem? How to know what is the right size of RAM? I can't put 40
> Go
> RAM or I'll go out of business...!
I agree with Josh's analysis. It's best to perform index and query tuning
to ensure data access is optimal before throwing hardware at the problem.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Stephane" <Stephane@.discussions.microsoft.com> wrote in message
news:55BD6AB5-E9FB-4F80-B58C-92CB26754A41@.microsoft.com...
> Hi,
> I got some performance problems. I have a 40 Go database on a dual core
> CPU
> with 2 Go of RAM.
> Sometime it's fast, sometime it's slow. Some request take more than 20
> seconds to complete. And if I re-run them right after, it executes in a
> flash
> even if I changed some paramaters.
> For instance, I have a 10 Go table with indexes for a total of 15 Go.
> Other
> tables where I run the most queries are 150 Mo with a 64 Mo index.
> Since the performance is not always the same, should I conclude this is a
> memory problem? How to know what is the right size of RAM? I can't put 40
> Go
> RAM or I'll go out of business...!
> Any idea would be appreciated.
> Thanks
> Stephane

40 Go database with 2 Go Ram?

Hi,
I got some performance problems. I have a 40 Go database on a dual core CPU
with 2 Go of RAM.
Sometime it's fast, sometime it's slow. Some request take more than 20
seconds to complete. And if I re-run them right after, it executes in a flash
even if I changed some paramaters.
For instance, I have a 10 Go table with indexes for a total of 15 Go. Other
tables where I run the most queries are 150 Mo with a 64 Mo index.
Since the performance is not always the same, should I conclude this is a
memory problem? How to know what is the right size of RAM? I can't put 40 Go
RAM or I'll go out of business...!
Any idea would be appreciated.
Thanks
Stephane
This may be a sign that you need better indexing. The slow/fast is what
happens when the server needs to read in and cache your new data, which got
flushed because it needed the space for old data. But it's hard to give any
general rule for a reasonable ratio of data to RAM. A ten gb table with 5gb
of indexes, still doesn't say whether 2gb is enough RAM. But remember, since
the OS typically takes half a gb to 1gb, going from 2gb RAM on a machine to
4gb, can almost triple your effective cache space, which is usually a good
thing!
Josh
"Stephane" wrote:

> Hi,
> I got some performance problems. I have a 40 Go database on a dual core CPU
> with 2 Go of RAM.
> Sometime it's fast, sometime it's slow. Some request take more than 20
> seconds to complete. And if I re-run them right after, it executes in a flash
> even if I changed some paramaters.
> For instance, I have a 10 Go table with indexes for a total of 15 Go. Other
> tables where I run the most queries are 150 Mo with a 64 Mo index.
> Since the performance is not always the same, should I conclude this is a
> memory problem? How to know what is the right size of RAM? I can't put 40 Go
> RAM or I'll go out of business...!
> Any idea would be appreciated.
> Thanks
> Stephane
|||> Since the performance is not always the same, should I conclude this is a
> memory problem? How to know what is the right size of RAM? I can't put 40
> Go
> RAM or I'll go out of business...!
I agree with Josh's analysis. It's best to perform index and query tuning
to ensure data access is optimal before throwing hardware at the problem.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stephane" <Stephane@.discussions.microsoft.com> wrote in message
news:55BD6AB5-E9FB-4F80-B58C-92CB26754A41@.microsoft.com...
> Hi,
> I got some performance problems. I have a 40 Go database on a dual core
> CPU
> with 2 Go of RAM.
> Sometime it's fast, sometime it's slow. Some request take more than 20
> seconds to complete. And if I re-run them right after, it executes in a
> flash
> even if I changed some paramaters.
> For instance, I have a 10 Go table with indexes for a total of 15 Go.
> Other
> tables where I run the most queries are 150 Mo with a 64 Mo index.
> Since the performance is not always the same, should I conclude this is a
> memory problem? How to know what is the right size of RAM? I can't put 40
> Go
> RAM or I'll go out of business...!
> Any idea would be appreciated.
> Thanks
> Stephane