Parameter Conversions Question from SQL Server

I have a question regarding a casting error I recieve when I explictly assign a value to a value in a stored procedure......
The value pstrLoginName is a public string defined in a code module
The stored procedure is NVARCHAR Datatypes....

Here is the Stored Procedure

CREATE procedure dbo.Appt_GetPermissions_NET
(
@.LoginName nvarchar(15),
@.Password NvarChar(15),
@.DeleteScan bit Output

)
as
select

@.DeleteScan=UserP_DeleteScan
from
Clinic_Users
where
UserName = @.LoginName
and
UserPassword = @.Password

GO

Here is my error

Specified cast is not valid
Line 194: cmdsql.CommandType = CommandType.StoredProcedure
Line 195:
Line 196: parmLogin = cmdsql.Parameters.Add("@.LoginName", SqlDbType.NVarChar, 15).Value = pstrLoginName
Line 197: parmPassword = cmdsql.Parameters.Add("@.LoginPassword", SqlDbType.NVarChar, 15).Value = pstrLoginPassword
Line 198: parmDelete = cmdsql.Parameters.Add("@.DeleteScan", SqlDbType.Bit)

my vb .net code to execute this

Public Sub ObtainPermission()

'get the needed data
Dim consql As New SqlConnection("Server=myserver;database=APPOINTMENTS;uid=webtest;pwd=webtest")
Dim cmdsql As New SqlCommand
Dim parmLogin As SqlParameter
Dim parmPassword As SqlParameter
Dim parmDelete As SqlParameter

cmdsql = New SqlCommand("Appt_GetPermissions_NET", consql)
cmdsql.CommandType = CommandType.StoredProcedure

parmLogin = cmdsql.Parameters.Add("@.LoginName", SqlDbType.NVarChar, 15).Value = pstrLoginName <--error occurs here

parmPassword = cmdsql.Parameters.Add("@.LoginPassword", SqlDbType.NVarChar, 15).Value = pstrLoginPassword <--I'm sure it will happen here then
parmDelete = cmdsql.Parameters.Add("@.DeleteScan", SqlDbType.Bit)
parmDelete.Direction = ParameterDirection.Output

consql.Open()
cmdsql.ExecuteNonQuery()

'obtain rights

pstrPermissions = cmdsql.Parameters("@.DeleteScan").Value
consql.Close()

End SubDude, your doing too much on one line!
If you are going to do all this on one line you'll have to add some paranthesis:
(parmLogin = cmdsql.Parameters.Add("@.LoginName", SqlDbType.NVarChar, 15)).Value = pstrLoginName

I'd recommend splitting the row in two lines for better readability.|||You have both this line:

Dim cmdsql As New SqlCommand

and this line:

cmdsql = New SqlCommand("Appt_GetPermissions_NET", consql)

Note that you have used "New" twice. Remove the "New" from the first line and you should have better luck.
Terri|||Ofcourse it's wrong to allocate an object twice,
but it shouldn't raise an error, since he hasn't used the variable
before allocating it the second time.
Won't it just 'leak' an object instance?sql

Parallel Development of SSIS packages

I have seen a number of posts regarding parallel development of SSIS packages and need some further information.

So far we have been developing SSIS packages along a single development stream and therefore have managed to avoid parallel development of our packages.

However, due to business pressures we will soon have multiple project streams running in parallel, and therefore multiple code branches, as part of that we will definitely need to redevelop the same SSIS packages in parallel. Judging from your post above and some testing we have done this is going to be a nightmare as we cannot merge the code. We can put in place processes to try and mitigate this but there are bound to be issues along the way.

Do you know whether this problem is going to be fixed? We are now using Team Foundation Server but presumably the merge algorythm used is same/similar to that of VSS and therefore very flaky?

However, not only are we having problems with the merging of the XML files, but we also use script tasks within the packages which are precompiled, as the DTSX files contain the binary objects associated with the script source code, if two developers change the same script task in isolated branches the binary is not recompiled as the merge software does not recognise this object.

Do you know whether these issues have been identified and are going to be fixed to be in line with the rest of Microsoft Configuration Managment principles of parallel development?

Many thanks.

[Microsoft follow-up] A question to be addressed here.|||

A. as long as no more then 1 developer works on the same dtsx package at a time there is no problem ( lock it in vss)

B. try to make the packages light, microsoft is not recommending heavy packages .

C. If you would like to merge codes from packages just copy all the elements from one package to the other ( use a container)

|||

In response to your points, I'm still not sure this is a satisfactory situtation for what is supposed to be an enterprise standard tool.

A - The problem we have is that we will have more than 1 developer working on the same package at the same time, and that those versions of packages will have to be deployed at different intervals. This is because of business priorities and is the normal development scenario on oither platforms such as .Net (C#) where you can have multiple branches with multiple developers working on the code. In these scenarios the Config Mgmt tool will wherever possible merge the source code automatically and raise conflicts wherever the code changes are in the same place to enable the Developer/Config Mgmt analyst to determine what cause of action to take. The problem we have here is the capability of the VSS or TFS to merge the DTSX packages is not accurate and our testing has proven that this sometimes works and more often doesn't.

Also say we deploy a package and that is running in production, as soon as it has gone live work on the next stream of development starts and the development team crack on making changes to the package. However, after a month in live a bug is identified in the package and an emergency fix is required to the DTSX package, the support team make this change to the live version to ensure that we fulfill the business needs as they cannot wait for the next development implementation for the fix. How do we merge that support change into the version of the package the development team are working on.... is this a manual process to physically open the development package as well and make the same changes in two places. Obviously this is very risky and introduces potential issues of developers knocking out support changes.

B - Wherever possible we have tried to keeping the packages light but specific ETL processes are always going to be quite intensive and include a lot of code, especially if working with a set of data within a pipeline.

C - When you mention merging the elements from one container to the other, how does this work if both version of packages also create new variables, add new connection managers, logging, checkpointing, error handlers or use the binary compiled objects from script tasks or even change the same script task.

I think the answer at the moment is that this cannot be done consistently and accurately but wanted to know whether Microsoft recognised this limitation of their product and how/if this would be addressed in future as the uptake and usage of SSIS increases, and this problem becomes more of an issue as development and support of ETL systems will be done in parallel.

Thanks.

|||

Hi Tom,

The SSIS team is aware of the issue, and your assessment is accurate. Right now SSIS packages essentially have to be treated as blobs, allowing only one person to work on them at a time because merging the XML is difficult. We have something in the works that should ease some of the problem, but we’re not sure if it will make it into the initial Katmai release at this point.

One alternative I’ve seen for projects/packages which change frequently is to switch to building the packages dynamically with code, instead of graphically in the UI. This makes the merging of changes much easier. However, the downside is that you lose the ease of use of the designer, and there can be a bit of learning curve for the object model.

Thanks,

~Matt

|||

Nice one Matt. Good to know that you guys are thinking about this. I'm interested to know what your " something in the works " is.

-Jamie

Parallel Development of SSIS packages

I have seen a number of posts regarding parallel development of SSIS packages and need some further information.

So far we have been developing SSIS packages along a single development stream and therefore have managed to avoid parallel development of our packages.

However, due to business pressures we will soon have multiple project streams running in parallel, and therefore multiple code branches, as part of that we will definitely need to redevelop the same SSIS packages in parallel. Judging from your post above and some testing we have done this is going to be a nightmare as we cannot merge the code. We can put in place processes to try and mitigate this but there are bound to be issues along the way.

Do you know whether this problem is going to be fixed? We are now using Team Foundation Server but presumably the merge algorythm used is same/similar to that of VSS and therefore very flaky?

However, not only are we having problems with the merging of the XML files, but we also use script tasks within the packages which are precompiled, as the DTSX files contain the binary objects associated with the script source code, if two developers change the same script task in isolated branches the binary is not recompiled as the merge software does not recognise this object.

Do you know whether these issues have been identified and are going to be fixed to be in line with the rest of Microsoft Configuration Managment principles of parallel development?

Many thanks.

[Microsoft follow-up] A question to be addressed here.|||

A. as long as no more then 1 developer works on the same dtsx package at a time there is no problem ( lock it in vss)

B. try to make the packages light, microsoft is not recommending heavy packages .

C. If you would like to merge codes from packages just copy all the elements from one package to the other ( use a container)

|||

In response to your points, I'm still not sure this is a satisfactory situtation for what is supposed to be an enterprise standard tool.

A - The problem we have is that we will have more than 1 developer working on the same package at the same time, and that those versions of packages will have to be deployed at different intervals. This is because of business priorities and is the normal development scenario on oither platforms such as .Net (C#) where you can have multiple branches with multiple developers working on the code. In these scenarios the Config Mgmt tool will wherever possible merge the source code automatically and raise conflicts wherever the code changes are in the same place to enable the Developer/Config Mgmt analyst to determine what cause of action to take. The problem we have here is the capability of the VSS or TFS to merge the DTSX packages is not accurate and our testing has proven that this sometimes works and more often doesn't.

Also say we deploy a package and that is running in production, as soon as it has gone live work on the next stream of development starts and the development team crack on making changes to the package. However, after a month in live a bug is identified in the package and an emergency fix is required to the DTSX package, the support team make this change to the live version to ensure that we fulfill the business needs as they cannot wait for the next development implementation for the fix. How do we merge that support change into the version of the package the development team are working on.... is this a manual process to physically open the development package as well and make the same changes in two places. Obviously this is very risky and introduces potential issues of developers knocking out support changes.

B - Wherever possible we have tried to keeping the packages light but specific ETL processes are always going to be quite intensive and include a lot of code, especially if working with a set of data within a pipeline.

C - When you mention merging the elements from one container to the other, how does this work if both version of packages also create new variables, add new connection managers, logging, checkpointing, error handlers or use the binary compiled objects from script tasks or even change the same script task.

I think the answer at the moment is that this cannot be done consistently and accurately but wanted to know whether Microsoft recognised this limitation of their product and how/if this would be addressed in future as the uptake and usage of SSIS increases, and this problem becomes more of an issue as development and support of ETL systems will be done in parallel.

Thanks.

|||

Hi Tom,

The SSIS team is aware of the issue, and your assessment is accurate. Right now SSIS packages essentially have to be treated as blobs, allowing only one person to work on them at a time because merging the XML is difficult. We have something in the works that should ease some of the problem, but we’re not sure if it will make it into the initial Katmai release at this point.

One alternative I’ve seen for projects/packages which change frequently is to switch to building the packages dynamically with code, instead of graphically in the UI. This makes the merging of changes much easier. However, the downside is that you lose the ease of use of the designer, and there can be a bit of learning curve for the object model.

Thanks,

~Matt

|||

Nice one Matt. Good to know that you guys are thinking about this. I'm interested to know what your " something in the works " is.

-Jamie

pageiolatch_sh

Hi,
We've been having problems with our SQL server 2000 regarding running
some queries. The server runs on a dual Xenon 2.8 G with 2Gb of RAM
machine
We run weekly a query on a 2 mil lines table which usually takes no
more than 1-2 hours using SQL query analyzer.
Now apparently with no reason at random points in the progress (no
matter select or update) the query enters sleep mode and shows
PAGEIOLATCH_SH as waittype. From now on it just does nothing for
hours. Other users are able to run other queries in parallel, but this
one query will only start running again in a very long time.
We don't know what's issuing this and it's dragging us down a lot.
Please let us know if any of you encountered this or has a solution.
Thanks
Thanks for your reply,
I'm afraid to set MAXDOP to 1 since there are some procedures that run
each 10 minutes and it might get ugly if those got stuck. Also I
wonder if there is an option to set the priority to run the queries
per user or such.
Here's the output of SHOWPLANS for the statement that got stuck in the
current query. It usualy runs in no longer than 10 mins but now it's
still sleeping on it since about 4 hours:
http://spreadsheets.google.com/pub?key=p3WxqiZShdsihvL5hvWLoMA
|||Please let us know if you find anything in particular that could
negatively affect the server's performance in the following
configuration:
name min value max
value config_value run_value
affinity mask-2147483648214748364700
allow updates0100
awe enabled0100
c2 audit mode0100
cost threshold for parallelism03276755
Cross DB Ownership Chaining0100
cursor threshold-12147483647-1-1
default full-text language0214748364710331033
default language0999900
fill factor (%)010000
index create memory (KB)704214748364700
lightweight pooling0100
locks5000214748364700
max degree of parallelism03200
max server memory (MB)4214748364721474836472147483647
max text repl size (B)021474836476553665536
max worker threads3232767255255
media retention036500
min memory per query (KB)512214748364710241024
min server memory (MB)0214748364700
nested triggers0111
network packet size (B)5126553640964096
open objects0214748364700
priority boost0111
query governor cost limit0214748364700
query wait (s)-12147483647-1-1
recovery interval (min)03276733
remote access0111
remote login timeout (s)021474836472020
remote proc trans0100
remote query timeout (s)02147483647600600
scan for startup procs0100
set working set size0100
show advanced options0111
two digit year cutoff1753999920492049
user connections03276700
user options03276700
|||That wait type is usually associated with disk to memory type actions and
generally means your disks are the bottle neck. Do you have a bad drive in
the array?
Andrew J. Kelly SQL MVP
<solutzii@.gmail.com> wrote in message
news:1174933662.357683.212020@.y80g2000hsf.googlegr oups.com...
> Please let us know if you find anything in particular that could
> negatively affect the server's performance in the following
> configuration:
> name min value max
> value config_value run_value
> affinity mask -2147483648 2147483647 0 0
> allow updates 0 1 0 0
> awe enabled 0 1 0 0
> c2 audit mode 0 1 0 0
> cost threshold for parallelism 0 32767 5 5
> Cross DB Ownership Chaining 0 1 0 0
> cursor threshold -1 2147483647 -1 -1
> default full-text language 0 2147483647 1033 1033
> default language 0 9999 0 0
> fill factor (%) 0 100 0 0
> index create memory (KB) 704 2147483647 0 0
> lightweight pooling 0 1 0 0
> locks 5000 2147483647 0 0
> max degree of parallelism 0 32 0 0
> max server memory (MB) 4 2147483647 2147483647 2147483647
> max text repl size (B) 0 2147483647 65536 65536
> max worker threads 32 32767 255 255
> media retention 0 365 0 0
> min memory per query (KB) 512 2147483647 1024 1024
> min server memory (MB) 0 2147483647 0 0
> nested triggers 0 1 1 1
> network packet size (B) 512 65536 4096 4096
> open objects 0 2147483647 0 0
> priority boost 0 1 1 1
> query governor cost limit 0 2147483647 0 0
> query wait (s) -1 2147483647 -1 -1
> recovery interval (min) 0 32767 3 3
> remote access 0 1 1 1
> remote login timeout (s) 0 2147483647 20 20
> remote proc trans 0 1 0 0
> remote query timeout (s) 0 2147483647 600 600
> scan for startup procs 0 1 0 0
> set working set size 0 1 0 0
> show advanced options 0 1 1 1
> two digit year cutoff 1753 9999 2049 2049
> user connections 0 32767 0 0
> user options 0 32767 0 0
>
|||You may be experiencing a User Mode Scheduler issue. Pretty rare but NASTY
when it arises. Do a search online for that issue. There is a workaround I
have seen floating around somewhere. MS tech support can be called to help
out also.
TheSQLGuru
President
Indicium Resources, Inc.
<solutzii@.gmail.com> wrote in message
news:1174910527.642712.269180@.y66g2000hsf.googlegr oups.com...
> Hi,
> We've been having problems with our SQL server 2000 regarding running
> some queries. The server runs on a dual Xenon 2.8 G with 2Gb of RAM
> machine
> We run weekly a query on a 2 mil lines table which usually takes no
> more than 1-2 hours using SQL query analyzer.
> Now apparently with no reason at random points in the progress (no
> matter select or update) the query enters sleep mode and shows
> PAGEIOLATCH_SH as waittype. From now on it just does nothing for
> hours. Other users are able to run other queries in parallel, but this
> one query will only start running again in a very long time.
> We don't know what's issuing this and it's dragging us down a lot.
> Please let us know if any of you encountered this or has a solution.
> Thanks
>
|||Priority Boost should almost NEVER be set to one. That is the only thing
that jumped out at me as an 'incorrect' setting. Also, unless you have a
lot of cpu's (as in 8+ cores, not hyperthreaded) and a VERY good I/O
subsystem, I would up the cost threshhold for parallelism to 20-50 (testing
out to see effect.
TheSQLGuru
President
Indicium Resources, Inc.
<solutzii@.gmail.com> wrote in message
news:1174933662.357683.212020@.y80g2000hsf.googlegr oups.com...
> Please let us know if you find anything in particular that could
> negatively affect the server's performance in the following
> configuration:
> name min value max
> value config_value run_value
> affinity mask -2147483648 2147483647 0 0
> allow updates 0 1 0 0
> awe enabled 0 1 0 0
> c2 audit mode 0 1 0 0
> cost threshold for parallelism 0 32767 5 5
> Cross DB Ownership Chaining 0 1 0 0
> cursor threshold -1 2147483647 -1 -1
> default full-text language 0 2147483647 1033 1033
> default language 0 9999 0 0
> fill factor (%) 0 100 0 0
> index create memory (KB) 704 2147483647 0 0
> lightweight pooling 0 1 0 0
> locks 5000 2147483647 0 0
> max degree of parallelism 0 32 0 0
> max server memory (MB) 4 2147483647 2147483647 2147483647
> max text repl size (B) 0 2147483647 65536 65536
> max worker threads 32 32767 255 255
> media retention 0 365 0 0
> min memory per query (KB) 512 2147483647 1024 1024
> min server memory (MB) 0 2147483647 0 0
> nested triggers 0 1 1 1
> network packet size (B) 512 65536 4096 4096
> open objects 0 2147483647 0 0
> priority boost 0 1 1 1
> query governor cost limit 0 2147483647 0 0
> query wait (s) -1 2147483647 -1 -1
> recovery interval (min) 0 32767 3 3
> remote access 0 1 1 1
> remote login timeout (s) 0 2147483647 20 20
> remote proc trans 0 1 0 0
> remote query timeout (s) 0 2147483647 600 600
> scan for startup procs 0 1 0 0
> set working set size 0 1 0 0
> show advanced options 0 1 1 1
> two digit year cutoff 1753 9999 2049 2049
> user connections 0 32767 0 0
> user options 0 32767 0 0
>
|||Thank you for your suggestions
I am taking the query I was talking above stept by step and it works
like 30x slower than it should be. I am experimenting different
setting on this query and stopping it if it does not complete in about
5 minutes. Running it weekly for months I know for sure that this one
should take no more than 1-2 minutes.
- I have set the priority boost to 0, no noticeable performance change
till now.
- I ran scandisk on this drive that we never had a problem with, and
no errors appeared. I did notice however that there might be a
bottleneck on the hard drive maybe due to some procedures that run on
the server periodically, yet these procedures have always been there.
Though I'm the only one running on the server right now, the update
statement mentioned here is put on PAGEIOLATCH just a second away
since I run it.
The CPU is idle as well as the Hard drive. I will get some more info
on the User Mode Scheduler issue in the meantime. Let me know if you
have any other ideas.
Regards
|||I looked all day over the internet trying to find solutions but none
worked. The query finally ran at night and it ran incredibly fast.
Either the OS makes the server put the query on hold at some times or
the Server itself does that. This did not happen until a couple of
weeks ago and the only thing that changed since then is the size of
the weekly table, which crossed 2 mil rows. Can anyone make a
connection to any setting ?
|||Are you sure it isn't blocked? What does sp_who2 show when it is waiting?
Andrew J. Kelly SQL MVP
<solutzii@.gmail.com> wrote in message
news:1175108694.916927.56640@.l77g2000hsb.googlegro ups.com...
>I looked all day over the internet trying to find solutions but none
> worked. The query finally ran at night and it ran incredibly fast.
> Either the OS makes the server put the query on hold at some times or
> the Server itself does that. This did not happen until a couple of
> weeks ago and the only thing that changed since then is the size of
> the weekly table, which crossed 2 mil rows. Can anyone make a
> connection to any setting ?
>
|||These are the interest columns showed by sp_who2 for part of the
query : One instance shows PAGEIOLATCH_SH as Wait Type in the
Enterprise Manager activity
spidstatusblkbycommandCPUtimeDiskIOProgram name
62sleeping . UPDATE 128012748918
SQL Query Analyzer
62sleeping . UPDATE 2859748918
SQL Query Analyzer
62sleeping . UPDATE 3203748918
SQL Query Analyzer
It can sleep for hours at this query that it sometimes runs in 3-4
minutes. I can see the disk read queue length and disk activity
constant raised at 200 and the processor at 1% in the perfmon on the
server while it sleeps.
Also, TheSQLGuru now the guys around are unhappy that I set the
priority boost to 0 though no change happened. Can you give us a
reason for not setting it to 1?
Thank you

pageiolatch_sh

Hi,
We've been having problems with our SQL server 2000 regarding running
some queries. The server runs on a dual Xenon 2.8 G with 2Gb of RAM
machine
We run weekly a query on a 2 mil lines table which usually takes no
more than 1-2 hours using SQL query analyzer.
Now apparently with no reason at random points in the progress (no
matter select or update) the query enters sleep mode and shows
PAGEIOLATCH_SH as waittype. From now on it just does nothing for
hours. Other users are able to run other queries in parallel, but this
one query will only start running again in a very long time.
We don't know what's issuing this and it's dragging us down a lot.
Please let us know if any of you encountered this or has a solution.
ThanksWhat might be useful is if you posted the SHOWPLANS
set showplan_all on
go
go
go
set showplan_all off
go
Also, as a guess you could try using the OPTION (MAXDOP 1) , if you
suspect it's a parallelism issue
Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com
<solutzii@.gmail.com> wrote in message
news:1174910527.642712.269180@.y66g2000hsf.googlegroups.com...
> Hi,
> We've been having problems with our SQL server 2000 regarding running
> some queries. The server runs on a dual Xenon 2.8 G with 2Gb of RAM
> machine
> We run weekly a query on a 2 mil lines table which usually takes no
> more than 1-2 hours using SQL query analyzer.
> Now apparently with no reason at random points in the progress (no
> matter select or update) the query enters sleep mode and shows
> PAGEIOLATCH_SH as waittype. From now on it just does nothing for
> hours. Other users are able to run other queries in parallel, but this
> one query will only start running again in a very long time.
> We don't know what's issuing this and it's dragging us down a lot.
> Please let us know if any of you encountered this or has a solution.
> Thanks
>|||Thanks for your reply,
I'm afraid to set MAXDOP to 1 since there are some procedures that run
each 10 minutes and it might get ugly if those got stuck. Also I
wonder if there is an option to set the priority to run the queries
per user or such.
Here's the output of SHOWPLANS for the statement that got stuck in the
current query. It usualy runs in no longer than 10 mins but now it's
still sleeping on it since about 4 hours:
http://spreadsheets.google.com/pub?key=p3WxqiZShdsihvL5hvWLoMA|||Please let us know if you find anything in particular that could
negatively affect the server's performance in the following
configuration:
name min value max
value config_value run_value
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 0 0
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 5 5
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 2147483647 2147483647
max text repl size (B) 0 2147483647 65536 65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 65536 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 1 1
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 3 3
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0|||That wait type is usually associated with disk to memory type actions and
generally means your disks are the bottle neck. Do you have a bad drive in
the array?
--
Andrew J. Kelly SQL MVP
<solutzii@.gmail.com> wrote in message
news:1174933662.357683.212020@.y80g2000hsf.googlegroups.com...
> Please let us know if you find anything in particular that could
> negatively affect the server's performance in the following
> configuration:
> name min value max
> value config_value run_value
> affinity mask -2147483648 2147483647 0 0
> allow updates 0 1 0 0
> awe enabled 0 1 0 0
> c2 audit mode 0 1 0 0
> cost threshold for parallelism 0 32767 5 5
> Cross DB Ownership Chaining 0 1 0 0
> cursor threshold -1 2147483647 -1 -1
> default full-text language 0 2147483647 1033 1033
> default language 0 9999 0 0
> fill factor (%) 0 100 0 0
> index create memory (KB) 704 2147483647 0 0
> lightweight pooling 0 1 0 0
> locks 5000 2147483647 0 0
> max degree of parallelism 0 32 0 0
> max server memory (MB) 4 2147483647 2147483647 2147483647
> max text repl size (B) 0 2147483647 65536 65536
> max worker threads 32 32767 255 255
> media retention 0 365 0 0
> min memory per query (KB) 512 2147483647 1024 1024
> min server memory (MB) 0 2147483647 0 0
> nested triggers 0 1 1 1
> network packet size (B) 512 65536 4096 4096
> open objects 0 2147483647 0 0
> priority boost 0 1 1 1
> query governor cost limit 0 2147483647 0 0
> query wait (s) -1 2147483647 -1 -1
> recovery interval (min) 0 32767 3 3
> remote access 0 1 1 1
> remote login timeout (s) 0 2147483647 20 20
> remote proc trans 0 1 0 0
> remote query timeout (s) 0 2147483647 600 600
> scan for startup procs 0 1 0 0
> set working set size 0 1 0 0
> show advanced options 0 1 1 1
> two digit year cutoff 1753 9999 2049 2049
> user connections 0 32767 0 0
> user options 0 32767 0 0
>|||You may be experiencing a User Mode Scheduler issue. Pretty rare but NASTY
when it arises. Do a search online for that issue. There is a workaround I
have seen floating around somewhere. MS tech support can be called to help
out also.
--
TheSQLGuru
President
Indicium Resources, Inc.
<solutzii@.gmail.com> wrote in message
news:1174910527.642712.269180@.y66g2000hsf.googlegroups.com...
> Hi,
> We've been having problems with our SQL server 2000 regarding running
> some queries. The server runs on a dual Xenon 2.8 G with 2Gb of RAM
> machine
> We run weekly a query on a 2 mil lines table which usually takes no
> more than 1-2 hours using SQL query analyzer.
> Now apparently with no reason at random points in the progress (no
> matter select or update) the query enters sleep mode and shows
> PAGEIOLATCH_SH as waittype. From now on it just does nothing for
> hours. Other users are able to run other queries in parallel, but this
> one query will only start running again in a very long time.
> We don't know what's issuing this and it's dragging us down a lot.
> Please let us know if any of you encountered this or has a solution.
> Thanks
>|||Priority Boost should almost NEVER be set to one. That is the only thing
that jumped out at me as an 'incorrect' setting. Also, unless you have a
lot of cpu's (as in 8+ cores, not hyperthreaded) and a VERY good I/O
subsystem, I would up the cost threshhold for parallelism to 20-50 (testing
out to see effect.
--
TheSQLGuru
President
Indicium Resources, Inc.
<solutzii@.gmail.com> wrote in message
news:1174933662.357683.212020@.y80g2000hsf.googlegroups.com...
> Please let us know if you find anything in particular that could
> negatively affect the server's performance in the following
> configuration:
> name min value max
> value config_value run_value
> affinity mask -2147483648 2147483647 0 0
> allow updates 0 1 0 0
> awe enabled 0 1 0 0
> c2 audit mode 0 1 0 0
> cost threshold for parallelism 0 32767 5 5
> Cross DB Ownership Chaining 0 1 0 0
> cursor threshold -1 2147483647 -1 -1
> default full-text language 0 2147483647 1033 1033
> default language 0 9999 0 0
> fill factor (%) 0 100 0 0
> index create memory (KB) 704 2147483647 0 0
> lightweight pooling 0 1 0 0
> locks 5000 2147483647 0 0
> max degree of parallelism 0 32 0 0
> max server memory (MB) 4 2147483647 2147483647 2147483647
> max text repl size (B) 0 2147483647 65536 65536
> max worker threads 32 32767 255 255
> media retention 0 365 0 0
> min memory per query (KB) 512 2147483647 1024 1024
> min server memory (MB) 0 2147483647 0 0
> nested triggers 0 1 1 1
> network packet size (B) 512 65536 4096 4096
> open objects 0 2147483647 0 0
> priority boost 0 1 1 1
> query governor cost limit 0 2147483647 0 0
> query wait (s) -1 2147483647 -1 -1
> recovery interval (min) 0 32767 3 3
> remote access 0 1 1 1
> remote login timeout (s) 0 2147483647 20 20
> remote proc trans 0 1 0 0
> remote query timeout (s) 0 2147483647 600 600
> scan for startup procs 0 1 0 0
> set working set size 0 1 0 0
> show advanced options 0 1 1 1
> two digit year cutoff 1753 9999 2049 2049
> user connections 0 32767 0 0
> user options 0 32767 0 0
>|||Thank you for your suggestions
I am taking the query I was talking above stept by step and it works
like 30x slower than it should be. I am experimenting different
setting on this query and stopping it if it does not complete in about
5 minutes. Running it weekly for months I know for sure that this one
should take no more than 1-2 minutes.
- I have set the priority boost to 0, no noticeable performance change
till now.
- I ran scandisk on this drive that we never had a problem with, and
no errors appeared. I did notice however that there might be a
bottleneck on the hard drive maybe due to some procedures that run on
the server periodically, yet these procedures have always been there.
Though I'm the only one running on the server right now, the update
statement mentioned here is put on PAGEIOLATCH just a second away
since I run it.
The CPU is idle as well as the Hard drive. I will get some more info
on the User Mode Scheduler issue in the meantime. Let me know if you
have any other ideas.
Regards|||I looked all day over the internet trying to find solutions but none
worked. The query finally ran at night and it ran incredibly fast.
Either the OS makes the server put the query on hold at some times or
the Server itself does that. This did not happen until a couple of
weeks ago and the only thing that changed since then is the size of
the weekly table, which crossed 2 mil rows. Can anyone make a
connection to any setting ?|||Are you sure it isn't blocked? What does sp_who2 show when it is waiting?
--
Andrew J. Kelly SQL MVP
<solutzii@.gmail.com> wrote in message
news:1175108694.916927.56640@.l77g2000hsb.googlegroups.com...
>I looked all day over the internet trying to find solutions but none
> worked. The query finally ran at night and it ran incredibly fast.
> Either the OS makes the server put the query on hold at some times or
> the Server itself does that. This did not happen until a couple of
> weeks ago and the only thing that changed since then is the size of
> the weekly table, which crossed 2 mil rows. Can anyone make a
> connection to any setting ?
>|||These are the interest columns showed by sp_who2 for part of the
query : One instance shows PAGEIOLATCH_SH as Wait Type in the
Enterprise Manager activity
spid status blkby command CPUtime DiskIO Program name
62 sleeping . UPDATE 128012 748918
SQL Query Analyzer
62 sleeping . UPDATE 2859 748918
SQL Query Analyzer
62 sleeping . UPDATE 3203 748918
SQL Query Analyzer
It can sleep for hours at this query that it sometimes runs in 3-4
minutes. I can see the disk read queue length and disk activity
constant raised at 200 and the processor at 1% in the perfmon on the
server while it sleeps.
Also, TheSQLGuru now the guys around are unhappy that I set the
priority boost to 0 though no change happened. Can you give us a
reason for not setting it to 1?
Thank you|||If you have a disk Q of 200 then you have problems. Your processors will be
mostly idle since they can't do much until they wait for the data from the
disks. The question is what is causing such a high Disk Queue? What
edition of SQL Server and what service pack are you running? Have you
looked at the estimated query plan to see why it may be requiring so much
disk access? Which disk is the Q on, the log or data? Hopefully you have
the log files ona separate physical hard drive than the data files. I am
not convinced it is not a hardware problem with the disk or the disk
controller. Have you tried setting the MAXDOP to 1 for this update
statement? What about updating in smaller batches instead of all 2 million
rows at once. And setting the boost option rarely results in better
performance and can often be worse.
--
Andrew J. Kelly SQL MVP
<solutzii@.gmail.com> wrote in message
news:1175270891.547023.91370@.e65g2000hsc.googlegroups.com...
> These are the interest columns showed by sp_who2 for part of the
> query : One instance shows PAGEIOLATCH_SH as Wait Type in the
> Enterprise Manager activity
> spid status blkby command CPUtime DiskIO Program name
> 62 sleeping . UPDATE 128012 748918
> SQL Query Analyzer
> 62 sleeping . UPDATE 2859 748918
> SQL Query Analyzer
> 62 sleeping . UPDATE 3203 748918
> SQL Query Analyzer
> It can sleep for hours at this query that it sometimes runs in 3-4
> minutes. I can see the disk read queue length and disk activity
> constant raised at 200 and the processor at 1% in the perfmon on the
> server while it sleeps.
> Also, TheSQLGuru now the guys around are unhappy that I set the
> priority boost to 0 though no change happened. Can you give us a
> reason for not setting it to 1?
> Thank you
>|||I'd like to put an ON DELETE trigger on a table that will let me
determine what the SQL statement was that caused the DELETE action to
happen. Any ideas?
--
Japheth Nolt
Microsoft SBF Specialist
Landis Computer
www.landiscomputer.com
3/30/2007 2:47:24 PM
Andrew J. Kelly wrote:
> If you have a disk Q of 200 then you have problems. Your processors
> will be mostly idle since they can't do much until they wait for the
> data from the disks. The question is what is causing such a high Disk
> Queue? What edition of SQL Server and what service pack are you
> running? Have you looked at the estimated query plan to see why it
> may be requiring so much disk access? Which disk is the Q on, the log
> or data? Hopefully you have the log files ona separate physical
> hard drive than the data files. I am not convinced it is not a
> hardware problem with the disk or the disk controller. Have you
> tried setting the MAXDOP to 1 for this update statement? What about
> updating in smaller batches instead of all 2 million rows at once.
> And setting the boost option rarely results in better performance and
> can often be worse.|||I am not sure why you posted to this thread but that will do nothing for
you. You should run a trace if you want to see what statements are being
issued. Check out Profiler or Trace in BooksOnLine for more details.
--
Andrew J. Kelly SQL MVP
"Japheth Nolt" <japheth.remove@.landiscomputer.com> wrote in message
news:xn0f4bpjtyx5mx002@.msnews.microsoft.com...
> I'd like to put an ON DELETE trigger on a table that will let me
> determine what the SQL statement was that caused the DELETE action to
> happen. Any ideas?
> --
> Japheth Nolt
> Microsoft SBF Specialist
> Landis Computer
> www.landiscomputer.com
> 3/30/2007 2:47:24 PM
>
> Andrew J. Kelly wrote:
>> If you have a disk Q of 200 then you have problems. Your processors
>> will be mostly idle since they can't do much until they wait for the
>> data from the disks. The question is what is causing such a high Disk
>> Queue? What edition of SQL Server and what service pack are you
>> running? Have you looked at the estimated query plan to see why it
>> may be requiring so much disk access? Which disk is the Q on, the log
>> or data? Hopefully you have the log files ona separate physical
>> hard drive than the data files. I am not convinced it is not a
>> hardware problem with the disk or the disk controller. Have you
>> tried setting the MAXDOP to 1 for this update statement? What about
>> updating in smaller batches instead of all 2 million rows at once.
>> And setting the boost option rarely results in better performance and
>> can often be worse.|||I'm sorry. I hit post reply instead of post new. Thanks for your
help, though.
--
Japheth Nolt
Microsoft SBF Specialist
Landis Computer
www.landiscomputer.com
3/30/2007 3:07:42 PM
Andrew J. Kelly wrote:
> I am not sure why you posted to this thread but that will do nothing
> for you. You should run a trace if you want to see what statements
> are being issued. Check out Profiler or Trace in BooksOnLine for
> more details.|||Thanks Andrew,
We're using SQL Server 2000 - SP3. We also don't understand why the
Disk queue grows like that and what's causing it.
I just ran the query with max dop = 1 and didn't believe it ran in 3
mins. I ran it again and it got sleeping again and had to stop it
after 10 mins of 0%processor time and 100 Disk queue and 100 % Disk
time.
The estimated plan for this query shows 57% and index seek and 37% a
table scan.
Also tried spliting the table in half and got the same results. And
you are right, I am also not convinced it is not a hardware problem
with the disk or the disk controller. Most intrigueing is that the
query sometimes runs well, otherwise we would for sure change some
hardware.
We don't have the log on separate disk, I'm going to work on that and
let know the disk queue results.|||So I separated the log file and the constant high disk queue with a
1MB/s Disk read is found on the data file disk.
I found a identical problem on an older topic, with no solution
however -- http://www.mcse.ms/message600023.html.
The pagiolatch_sh wait type appears just when it wants during the
query (at least I haven't found any patten) and once this happens the
query takes 10-20 times more to finish, as well as the disk read is
while it normally goes over 20MB/s. I also tried to copy the database
file over to another disk to make sure there is no problem with the
disk and it copies without any problem.
The wait resource is constantly changing during all the time of course
since the wait time is no more than a few hundred ms, usually below
100, however disk read and avg read quee remain constant a very long
time as described above.|||You may want to try upgrading to SP4 or maybe even SQL2005 to try and rule
out bugs with SQL Server. SP4 added some diagnostics to help identify I/O
issues. If that doesn't help then I would have to day it sounds like
hardware issues.
--
Andrew J. Kelly SQL MVP
<solutzii@.gmail.com> wrote in message
news:1176237959.991174.116290@.v33g2000cwv.googlegroups.com...
> So I separated the log file and the constant high disk queue with a
> 1MB/s Disk read is found on the data file disk.
> I found a identical problem on an older topic, with no solution
> however -- http://www.mcse.ms/message600023.html.
> The pagiolatch_sh wait type appears just when it wants during the
> query (at least I haven't found any patten) and once this happens the
> query takes 10-20 times more to finish, as well as the disk read is
> while it normally goes over 20MB/s. I also tried to copy the database
> file over to another disk to make sure there is no problem with the
> disk and it copies without any problem.
> The wait resource is constantly changing during all the time of course
> since the wait time is no more than a few hundred ms, usually below
> 100, however disk read and avg read quee remain constant a very long
> time as described above.
>|||Thanks Andrew, maybe upgrading would have done the trick as I do not
yet know whether this is a 2k speciffic issue or not. I've been
monitoring all system counters these days and thought it might just be
a damn IO subsystem issue.
Reading all sort of documents and forum posts every day the last
couple of weeks really worn me out as I became obsessed with this
problem. However it was not in vain, and all I can say is that after I
tries all kind of tweaks the hint that placed me on a right track was
this post :
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/a0fd0f809e132760/ccd8be0513631918?q=pageiolatch_sh&lnk=nl&
I had read this thread before, but just untill the last post which I
only found a few days ago. This thread describes a problem with the
greatest similarity to ours.
I find it hard to believe that this problem is not wide spread out. I
forgot to mention that we added about 6 more columns before the
problem appeared, but I really did not think of it as a problem, yet
what Jami Bradley describes there makes sense. It turned out that
after many updates during our query, the number of forwarded records
increased significantly which probably lead to the optimizer problem
described in the post above. It helped a lot using DBCC SHOWCONTIG
WITH TABLERESULTS. I don't know yet whether there's a fix for this
problem, but the workaround we managed to successfully apply was
redesigning the table in its very detail. Thus we managed to bring the
number of pages of such a table to half, and drop the forwardedrecords
to 0. Now the query runs fine as it should have.
Many thanks to everyone, and please post here if you know more about
this issue.|||I didn't realize you had a heap. If the table was heavily fragmented and
had lots of forwarding pointers it can certainly use a LOT more I/O than
necessary.
--
Andrew J. Kelly SQL MVP
<solutzii@.gmail.com> wrote in message
news:1176734808.118790.199430@.o5g2000hsb.googlegroups.com...
> Thanks Andrew, maybe upgrading would have done the trick as I do not
> yet know whether this is a 2k speciffic issue or not. I've been
> monitoring all system counters these days and thought it might just be
> a damn IO subsystem issue.
> Reading all sort of documents and forum posts every day the last
> couple of weeks really worn me out as I became obsessed with this
> problem. However it was not in vain, and all I can say is that after I
> tries all kind of tweaks the hint that placed me on a right track was
> this post :
> http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/a0fd0f809e132760/ccd8be0513631918?q=pageiolatch_sh&lnk=nl&
> I had read this thread before, but just untill the last post which I
> only found a few days ago. This thread describes a problem with the
> greatest similarity to ours.
> I find it hard to believe that this problem is not wide spread out. I
> forgot to mention that we added about 6 more columns before the
> problem appeared, but I really did not think of it as a problem, yet
> what Jami Bradley describes there makes sense. It turned out that
> after many updates during our query, the number of forwarded records
> increased significantly which probably lead to the optimizer problem
> described in the post above. It helped a lot using DBCC SHOWCONTIG
> WITH TABLERESULTS. I don't know yet whether there's a fix for this
> problem, but the workaround we managed to successfully apply was
> redesigning the table in its very detail. Thus we managed to bring the
> number of pages of such a table to half, and drop the forwardedrecords
> to 0. Now the query runs fine as it should have.
> Many thanks to everyone, and please post here if you know more about
> this issue.
>

pageiolatch_sh

Hi,
We've been having problems with our SQL server 2000 regarding running
some queries. The server runs on a dual Xenon 2.8 G with 2Gb of RAM
machine
We run weekly a query on a 2 mil lines table which usually takes no
more than 1-2 hours using SQL query analyzer.
Now apparently with no reason at random points in the progress (no
matter select or update) the query enters sleep mode and shows
PAGEIOLATCH_SH as waittype. From now on it just does nothing for
hours. Other users are able to run other queries in parallel, but this
one query will only start running again in a very long time.
We don't know what's issuing this and it's dragging us down a lot.
Please let us know if any of you encountered this or has a solution.
ThanksWhat might be useful is if you posted the SHOWPLANS
set showplan_all on
go
go
go
set showplan_all off
go
Also, as a guess you could try using the OPTION (MAXDOP 1) , if you
suspect it's a parallelism issue
Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com
<solutzii@.gmail.com> wrote in message
news:1174910527.642712.269180@.y66g2000hsf.googlegroups.com...
> Hi,
> We've been having problems with our SQL server 2000 regarding running
> some queries. The server runs on a dual Xenon 2.8 G with 2Gb of RAM
> machine
> We run weekly a query on a 2 mil lines table which usually takes no
> more than 1-2 hours using SQL query analyzer.
> Now apparently with no reason at random points in the progress (no
> matter select or update) the query enters sleep mode and shows
> PAGEIOLATCH_SH as waittype. From now on it just does nothing for
> hours. Other users are able to run other queries in parallel, but this
> one query will only start running again in a very long time.
> We don't know what's issuing this and it's dragging us down a lot.
> Please let us know if any of you encountered this or has a solution.
> Thanks
>|||Thanks for your reply,
I'm afraid to set MAXDOP to 1 since there are some procedures that run
each 10 minutes and it might get ugly if those got stuck. Also I
wonder if there is an option to set the priority to run the queries
per user or such.
Here's the output of SHOWPLANS for the statement that got stuck in the
current query. It usualy runs in no longer than 10 mins but now it's
still sleeping on it since about 4 hours:
http://spreadsheets.google.com/pub?...hdsihvL5hvWLoMA|||Please let us know if you find anything in particular that could
negatively affect the server's performance in the following
configuration:
name min value max
value config_value run_value
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 0 0
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 5 5
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 2147483647 2147483647
max text repl size (B) 0 2147483647 65536 65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 65536 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 1 1
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 3 3
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0|||That wait type is usually associated with disk to memory type actions and
generally means your disks are the bottle neck. Do you have a bad drive in
the array?
Andrew J. Kelly SQL MVP
<solutzii@.gmail.com> wrote in message
news:1174933662.357683.212020@.y80g2000hsf.googlegroups.com...
> Please let us know if you find anything in particular that could
> negatively affect the server's performance in the following
> configuration:
> name min value max
> value config_value run_value
> affinity mask -2147483648 2147483647 0 0
> allow updates 0 1 0 0
> awe enabled 0 1 0 0
> c2 audit mode 0 1 0 0
> cost threshold for parallelism 0 32767 5 5
> Cross DB Ownership Chaining 0 1 0 0
> cursor threshold -1 2147483647 -1 -1
> default full-text language 0 2147483647 1033 1033
> default language 0 9999 0 0
> fill factor (%) 0 100 0 0
> index create memory (KB) 704 2147483647 0 0
> lightweight pooling 0 1 0 0
> locks 5000 2147483647 0 0
> max degree of parallelism 0 32 0 0
> max server memory (MB) 4 2147483647 2147483647 2147483647
> max text repl size (B) 0 2147483647 65536 65536
> max worker threads 32 32767 255 255
> media retention 0 365 0 0
> min memory per query (KB) 512 2147483647 1024 1024
> min server memory (MB) 0 2147483647 0 0
> nested triggers 0 1 1 1
> network packet size (B) 512 65536 4096 4096
> open objects 0 2147483647 0 0
> priority boost 0 1 1 1
> query governor cost limit 0 2147483647 0 0
> query wait (s) -1 2147483647 -1 -1
> recovery interval (min) 0 32767 3 3
> remote access 0 1 1 1
> remote login timeout (s) 0 2147483647 20 20
> remote proc trans 0 1 0 0
> remote query timeout (s) 0 2147483647 600 600
> scan for startup procs 0 1 0 0
> set working set size 0 1 0 0
> show advanced options 0 1 1 1
> two digit year cutoff 1753 9999 2049 2049
> user connections 0 32767 0 0
> user options 0 32767 0 0
>|||You may be experiencing a User Mode Scheduler issue. Pretty rare but NASTY
when it arises. Do a search online for that issue. There is a workaround I
have seen floating around somewhere. MS tech support can be called to help
out also.
TheSQLGuru
President
Indicium Resources, Inc.
<solutzii@.gmail.com> wrote in message
news:1174910527.642712.269180@.y66g2000hsf.googlegroups.com...
> Hi,
> We've been having problems with our SQL server 2000 regarding running
> some queries. The server runs on a dual Xenon 2.8 G with 2Gb of RAM
> machine
> We run weekly a query on a 2 mil lines table which usually takes no
> more than 1-2 hours using SQL query analyzer.
> Now apparently with no reason at random points in the progress (no
> matter select or update) the query enters sleep mode and shows
> PAGEIOLATCH_SH as waittype. From now on it just does nothing for
> hours. Other users are able to run other queries in parallel, but this
> one query will only start running again in a very long time.
> We don't know what's issuing this and it's dragging us down a lot.
> Please let us know if any of you encountered this or has a solution.
> Thanks
>|||Priority Boost should almost NEVER be set to one. That is the only thing
that jumped out at me as an 'incorrect' setting. Also, unless you have a
lot of cpu's (as in 8+ cores, not hyperthreaded) and a VERY good I/O
subsystem, I would up the cost threshhold for parallelism to 20-50 (testing
out to see effect.
TheSQLGuru
President
Indicium Resources, Inc.
<solutzii@.gmail.com> wrote in message
news:1174933662.357683.212020@.y80g2000hsf.googlegroups.com...
> Please let us know if you find anything in particular that could
> negatively affect the server's performance in the following
> configuration:
> name min value max
> value config_value run_value
> affinity mask -2147483648 2147483647 0 0
> allow updates 0 1 0 0
> awe enabled 0 1 0 0
> c2 audit mode 0 1 0 0
> cost threshold for parallelism 0 32767 5 5
> Cross DB Ownership Chaining 0 1 0 0
> cursor threshold -1 2147483647 -1 -1
> default full-text language 0 2147483647 1033 1033
> default language 0 9999 0 0
> fill factor (%) 0 100 0 0
> index create memory (KB) 704 2147483647 0 0
> lightweight pooling 0 1 0 0
> locks 5000 2147483647 0 0
> max degree of parallelism 0 32 0 0
> max server memory (MB) 4 2147483647 2147483647 2147483647
> max text repl size (B) 0 2147483647 65536 65536
> max worker threads 32 32767 255 255
> media retention 0 365 0 0
> min memory per query (KB) 512 2147483647 1024 1024
> min server memory (MB) 0 2147483647 0 0
> nested triggers 0 1 1 1
> network packet size (B) 512 65536 4096 4096
> open objects 0 2147483647 0 0
> priority boost 0 1 1 1
> query governor cost limit 0 2147483647 0 0
> query wait (s) -1 2147483647 -1 -1
> recovery interval (min) 0 32767 3 3
> remote access 0 1 1 1
> remote login timeout (s) 0 2147483647 20 20
> remote proc trans 0 1 0 0
> remote query timeout (s) 0 2147483647 600 600
> scan for startup procs 0 1 0 0
> set working set size 0 1 0 0
> show advanced options 0 1 1 1
> two digit year cutoff 1753 9999 2049 2049
> user connections 0 32767 0 0
> user options 0 32767 0 0
>|||Thank you for your suggestions
I am taking the query I was talking above stept by step and it works
like 30x slower than it should be. I am experimenting different
setting on this query and stopping it if it does not complete in about
5 minutes. Running it weekly for months I know for sure that this one
should take no more than 1-2 minutes.
- I have set the priority boost to 0, no noticeable performance change
till now.
- I ran scandisk on this drive that we never had a problem with, and
no errors appeared. I did notice however that there might be a
bottleneck on the hard drive maybe due to some procedures that run on
the server periodically, yet these procedures have always been there.
Though I'm the only one running on the server right now, the update
statement mentioned here is put on PAGEIOLATCH just a second away
since I run it.
The CPU is idle as well as the Hard drive. I will get some more info
on the User Mode Scheduler issue in the meantime. Let me know if you
have any other ideas.
Regards|||I looked all day over the internet trying to find solutions but none
worked. The query finally ran at night and it ran incredibly fast.
Either the OS makes the server put the query on hold at some times or
the Server itself does that. This did not happen until a couple of
weeks ago and the only thing that changed since then is the size of
the weekly table, which crossed 2 mil rows. Can anyone make a
connection to any setting ?|||Are you sure it isn't blocked? What does sp_who2 show when it is waiting?
Andrew J. Kelly SQL MVP
<solutzii@.gmail.com> wrote in message
news:1175108694.916927.56640@.l77g2000hsb.googlegroups.com...
>I looked all day over the internet trying to find solutions but none
> worked. The query finally ran at night and it ran incredibly fast.
> Either the OS makes the server put the query on hold at some times or
> the Server itself does that. This did not happen until a couple of
> weeks ago and the only thing that changed since then is the size of
> the weekly table, which crossed 2 mil rows. Can anyone make a
> connection to any setting ?
>