Showing posts with label management. Show all posts
Showing posts with label management. Show all posts

Friday, March 23, 2012

Query Tuning problem.

Guys,
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.
The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)
Any idea what could be the issue here? The server
is not really heavily taxed.
The tables are small. They have very few rows.
VGNCCB_ROLE 939
VGNCCB_ROLE_JT 62389
VGNCCB_GROUP_USER_JT 1364
The problem Query:
select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))
I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.
TIA,
JackTry this query... I think the logic is the same... At least, it might help
you go in the right direction. You had a subquery within a subquery in your
where clause. This means that for every row of the outer table, the query
engine would have to do one subquery on the inner table and one subquery on
the inner table within that subquery for every row of the inner table!
That's a lot of work... Use JOINs instead:
SELECT
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM vign.VGNCCB_ROLE ROLE
JOIN vign.VGNCCB_ROLE_JT AS ROLE_JT ON ROLE.ROLE_ID = ROLE_JT.ROLE_ID
JOIN vign.VGNCCB_GROUP_USER_JT AS USER_JT
ON ROLE_JT.USER_NAME='testRole'
OR (ROLE_JT.GROUP_ID = USER_JT.GROUP_ID
AND USER_JT.USER_NAME = 'testRole'))
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fb6901c43e71$19861260$a001280a@.phx.gbl...
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
>
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack|||This did not work.
>--Original Message--
>Try this query... I think the logic is the same... At
least, it might help
>you go in the right direction. You had a subquery within
a subquery in your
>where clause. This means that for every row of the outer
table, the query
>engine would have to do one subquery on the inner table
and one subquery on
>the inner table within that subquery for every row of the
inner table!
>That's a lot of work... Use JOINs instead:
>SELECT
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
>FROM vign.VGNCCB_ROLE ROLE
>JOIN vign.VGNCCB_ROLE_JT AS ROLE_JT ON ROLE.ROLE_ID =ROLE_JT.ROLE_ID
>JOIN vign.VGNCCB_GROUP_USER_JT AS USER_JT
> ON ROLE_JT.USER_NAME='testRole'
> OR (ROLE_JT.GROUP_ID = USER_JT.GROUP_ID
> AND USER_JT.USER_NAME = 'testRole'))
>
>"Jack A" <anonymous@.discussions.microsoft.com> wrote in
message
>news:fb6901c43e71$19861260$a001280a@.phx.gbl...
>> Guys,
>> I'm stumped. While its not pertinent to the
>> matter, we are running a Vignette content management
>> system on Win2k with Sql 2000 Enterprise on a cluster.
>> The server has 2 Gig of RAM , 2 CPU's and the database
>> size is 1.5G.
>> The query below is fired at login. The indexes
>> seem fine based on the query plan. When I look through
>> profiler, the query below takes a very high # of CPU
>> cycles and reads. It consistently takes more than 1.5
>> seconds to execute the query below. I did a dbcc
pintable
>> for ALL the tables in the query and that did not help
>> either. It seemed to make it worse (3 seconds and above)
>> Any idea what could be the issue here? The server
>> is not really heavily taxed.
>> The tables are small. They have very few rows.
>> VGNCCB_ROLE 939
>> VGNCCB_ROLE_JT 62389
>> VGNCCB_GROUP_USER_JT 1364
>>
>> The problem Query:
>> select
>> ROLE_ID,
>> NAME,
>> DESCRIPTION,
>> CREATE_DATE,
>> MODIFIED_DATE
>> FROM
>> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
>> WHERE
>> ROLE_ID in
>> (select ROLE_ID
>> FROM
>> vign.VGNCCB_ROLE_JT -- Non clustered indexes
>> on USER_NAME AND non clustered on GROUP_ID
>> WHERE
>> USER_NAME = 'testRole' or GROUP_ID in (select
>> GROUP_ID
>> FROM
>> vign.VGNCCB_GROUP_USER_JT -- Non clustered
>> index on USER_NAME
>> WHERE
>> USER_NAME = 'testRole'))
>> I'd appreciate it if someone could follow me in this
>> thread to completion. Such a simple query should not
take
>> this long.
>>
>> TIA,
>> Jack
>
>.
>|||"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fc0701c43e8b$6948a000$a601280a@.phx.gbl...
> This did not work.
Are you going to elaborate, or is the issue closed?|||Does this query run any better?
SELECT R.ROLE_ID,
R.NAME,
R.DESCRIPTION,
R.CREATE_DATE,
R.MODIFIED_DATE
FROM vign.VGNCCB_ROLE R
INNER JOIN vign.VGNCCB_ROLE_JT RJT
ON RJT.ROLE_ID = R.ROLE_ID
WHERE RJT.USER_NAME = 'testRole'
UNION
SELECT R.ROLE_ID,
R.NAME,
R.DESCRIPTION,
R.CREATE_DATE,
R.MODIFIED_DATE
FROM vign.VGNCCB_ROLE R
INNER JOIN vign.VGNCCB_ROLE_JT RJT
ON RJT.ROLE_ID = R.ROLE_ID
INNER JOIN vign.VGNCCB_GROUP_USER_JT GUJT
ON GUJT.GROUP_ID = RJT.GROUP_ID
WHERE GUJT.USER_NAME = 'testRole'
Hope this helps,
Gert-Jan
Jack A wrote:
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack
--
(Please reply only to the newsgroup)

Tuesday, March 20, 2012

Query to list disk drives and capacity

I need a query to list all my disk drives, total disk capacity and free
space.. similar to what i would see in disk management.
I tried xp_fixeddrives and it just gives me the drives and free space, but
not total disk capacity
Thank you
Didn't you post this and get an answer already? I know I answered this same
question posted by someone a couple of weeks ago. Anyway, here it goes
again:
If you're using SQL 2000, do one of the following:
#1) Write an XP that retrieves this information for you,
#2) Get a Windows version of the "df.exe" program and use xp_cmdshell to
call it, and parse the results. There are freely available versions on the
Web and I previously posted a link to one the last time I answered this
question.
If you're using SQL 2005 write a SQLCLR table-valued function or SP to
retrieve the info using .NET.
"Hassan" <Hassan@.hotmail.com> wrote in message
news:unYp%23lrOHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I need a query to list all my disk drives, total disk capacity and free
>space.. similar to what i would see in disk management.
> I tried xp_fixeddrives and it just gives me the drives and free space, but
> not total disk capacity
> Thank you
>
|||It has always been one of my wishes for xp_fixeddrives to return more
complete disk info. I typically do this outside of SQL Server with tools such
as srvinfo.exe. I know this doesn't help if you want to insert the info into
a table.
Linchi
"Hassan" wrote:

> I need a query to list all my disk drives, total disk capacity and free
> space.. similar to what i would see in disk management.
> I tried xp_fixeddrives and it just gives me the drives and free space, but
> not total disk capacity
> Thank you
>
>

Query to list disk drives and capacity

I need a query to list all my disk drives, total disk capacity and free
space.. similar to what i would see in disk management.
I tried xp_fixeddrives and it just gives me the drives and free space, but
not total disk capacity
Thank youDidn't you post this and get an answer already? I know I answered this same
question posted by someone a couple of weeks ago. Anyway, here it goes
again:
If you're using SQL 2000, do one of the following:
#1) Write an XP that retrieves this information for you,
#2) Get a Windows version of the "df.exe" program and use xp_cmdshell to
call it, and parse the results. There are freely available versions on the
Web and I previously posted a link to one the last time I answered this
question.
If you're using SQL 2005 write a SQLCLR table-valued function or SP to
retrieve the info using .NET.
"Hassan" <Hassan@.hotmail.com> wrote in message
news:unYp%23lrOHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I need a query to list all my disk drives, total disk capacity and free
>space.. similar to what i would see in disk management.
> I tried xp_fixeddrives and it just gives me the drives and free space, but
> not total disk capacity
> Thank you
>|||It has always been one of my wishes for xp_fixeddrives to return more
complete disk info. I typically do this outside of SQL Server with tools such
as srvinfo.exe. I know this doesn't help if you want to insert the info into
a table.
Linchi
"Hassan" wrote:
> I need a query to list all my disk drives, total disk capacity and free
> space.. similar to what i would see in disk management.
> I tried xp_fixeddrives and it just gives me the drives and free space, but
> not total disk capacity
> Thank you
>
>

Query to list disk drives and capacity

I need a query to list all my disk drives, total disk capacity and free
space.. similar to what i would see in disk management.
I tried xp_fixeddrives and it just gives me the drives and free space, but
not total disk capacity
Thank youDidn't you post this and get an answer already? I know I answered this same
question posted by someone a couple of weeks ago. Anyway, here it goes
again:
If you're using SQL 2000, do one of the following:
#1) Write an XP that retrieves this information for you,
#2) Get a Windows version of the "df.exe" program and use xp_cmdshell to
call it, and parse the results. There are freely available versions on the
Web and I previously posted a link to one the last time I answered this
question.
If you're using SQL 2005 write a SQLCLR table-valued function or SP to
retrieve the info using .NET.
"Hassan" <Hassan@.hotmail.com> wrote in message
news:unYp%23lrOHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I need a query to list all my disk drives, total disk capacity and free
>space.. similar to what i would see in disk management.
> I tried xp_fixeddrives and it just gives me the drives and free space, but
> not total disk capacity
> Thank you
>|||It has always been one of my wishes for xp_fixeddrives to return more
complete disk info. I typically do this outside of SQL Server with tools suc
h
as srvinfo.exe. I know this doesn't help if you want to insert the info into
a table.
Linchi
"Hassan" wrote:

> I need a query to list all my disk drives, total disk capacity and free
> space.. similar to what i would see in disk management.
> I tried xp_fixeddrives and it just gives me the drives and free space, but
> not total disk capacity
> Thank you
>
>

Saturday, February 25, 2012

Query Time Out Expired

I am using a School Management software with MSSql Server 2005 software is working well on the server but it is not accessible on network. we using wareless network....Open [Start> All Programs> Microsoft SQL Server 2005> Configuration Tools> SQL Server Surface Area Configuration], select [MSSQLSERVER> Database Engine> Remote Connections], make sure that "Local and remote connections" option is selected.

Query that works in Management Studio, fails in Reporting Services

I can run the following query in Management Studio, but get the error listed below when I run it from the data tab in Reporting Services:

declare @.starttime as datetime
declare @.endtime as datetime
declare @.timezone as integer
declare @.date as datetime

set @.timezone = 1
set @.date = '5/1/2007'

set @.starttime = dateadd(hh, @.timezone, @.date)
set @.endtime = dateadd(d, 1, @.starttime)

select @.Starttime, @.endtime from site

Error Message:

TITLE: Microsoft Report Designer

An error occurred while executing the query.
The variable name '@.starttime' has already been declared. Variable names must be unique within a query batch or stored procedure.


ADDITIONAL INFORMATION:

The variable name '@.starttime' has already been declared. Variable names must be unique within a query batch or stored procedure. (Microsoft SQL Server, Error: 134)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=134&LinkId=20476


BUTTONS:

OK

What I am trying to accomplish is the ability for users to select which time zone they want the data in the report to display in. To do this, I created a timezone parameter that has the offset from Central Time (which is how all data is stored in our database).

Any help would be greatly appreciated!

Add the word "my" as a prefix to your variables:

DECLARE @.myStartTime smalldate time

etc...

Adamus

|||

Hi,

Please note that expressions in SSRS are case sensitive. In the code above, the starttime is declared as @.starttime and while referring to it in the select clause, you have typed it as @.Starttime. This is causing the conflict. SSRS created a report parameter, Starttime. Since SQL is not case sensitive, while execution it got two declarations for @.starttime and hence the error that you are facing.

After changing the Select clause, delete the parameter from Reports --> Parameters menu option and also from the dataset --> parameters Tab and your query should work.

Also, there is no need for the from clause.

HTH.

Regards,

Ashish

|||They are in fact case-sensitive and I've corrected it and the query now runs is RS. Thank you so much!!!