Page Numbers in Excel: queries

Showing posts with label queries. Show all posts

Friday, March 30, 2012

Parameter Queries in Analyser

Real new person question here but during my query practise I created some
parameter queries in the enterprise manager however theses will not work when
I open query analyser. Can anyone tell where I’ve boobed?
Many thanks
Sam
What did you create?
Stored procedures?
Which database - are you in the correct database?
Easiest to create them using query analyser using create proc then you can
save the script.
"Sam" wrote:

> Real new person question here but during my query practise I created some
> parameter queries in the enterprise manager however theses will not work when
> I open query analyser. Can anyone tell where I’ve boobed?
> Many thanks
> Sam
>

Parameter Queries in Access Projects

Hi, I am migrating an Access db to SQL Server.
I have a problem with my parameter queries.
In my access db, I have a lot of parameter quereis which I pass the parameters from forms, for example:

SELECT ... FROM... WHERE PID=[Forms]![frmPersonContact]![PersonID]

How can we do this in Access projects for views or stored procedure or user defined functions?

ThanksYou are probably better off replacing them with stored procedures. It won't be entirely the same; you can't simply link the value from a form to the parameter in the query. There will likely be some code involved.

Regards,

Hugh Scott

Originally posted by Sia
Hi, I am migrating an Access db to SQL Server.
I have a problem with my parameter queries.
In my access db, I have a lot of parameter quereis which I pass the parameters from forms, for example:

SELECT ... FROM... WHERE PID=[Forms]![frmPersonContact]![PersonID]

How can we do this in Access projects for views or stored procedure or user defined functions?

Thanks|||I know that its not easy but do you know of any sample that I can look at?

Thanks

Originally posted by hmscott
You are probably better off replacing them with stored procedures. It won't be entirely the same; you can't simply link the value from a form to the parameter in the query. There will likely be some code involved.

Regards,

Hugh Scott|||Consider this:

SQL Stored Procedure:

CREATE PROC spDoThis
@.Parm1 as Varchar(255),
@.Parm2 as Int

AS

UPDATE MyTable
SET Column1 = @.Parm1
WHERE ID = @.Parm2

ADO Code (in your form):

Private Function Update_Click()

Dim oConn, oComm

' Instantiate ActiveX objects
Set oConn = CreateObject("ADODB.Connection")
Set oComm = CreateObject("ADODB.Command")

' Set connection string
oConn.ConnectionString="Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security=False;" & _
"Catalogue=MyDatabase;" & _
"Server=MyServer"

oConn.Open

' Set the ADO Command to the name of the stored proc
Set oComm.ActiveConnection = oConn

oComm.CommandText = "spDoThis"
oComm.Parameters.Refresh

' Assign the values to the stored proc parameters from your form
oComm.Parameters("Parm1") = [MyForm].[MyText].[Value]
oComm.Parameters("Parm2") = [MyForm].[MyRecordID].[Value]
oComm.Execute

' Clean up ActiveX objects
Set oComm = Nothing
oConn.Close
Set oConn = Nothing

End Function

Notes:
1. This was written from the top of my head in a hurry (ie, don't nag me about syntax).
2. I have not tested this.
3. Your mileage may vary.

Hope this helps.

Regards,

Hugh Scott
Originally posted by Sia
I know that its not easy but do you know of any sample that I can look at?

Thankssql

Parameter Queries

If I understand what I am doing ...
I can run an unnamed parameter query in Query Analyzer
SELECT au_lname, au_fname
FROM dbo.authors
WHERE (state = ?)
However, that is about all I can do with it. I have not been able to do
anything with named parameter queries.
I guess the Books Online is a little off on these?
Is it possible to create some form of parameter query that accomplishes the
same thing as they do in Access?
Thanks.
Mike.You need a stored proc
--first create the procedure
Create Procedure prAuthorStates @.State char(2)
as
set nocount on
SELECT au_lname, au_fname,state
FROM dbo.authors
WHERE (state = @.State)
set nocount off
GO
--then execute the proc
exec prAuthorStates 'CA'
The code that you potsde can be used from ASP/PHP/JSP etc by using
prepared statements but not from Query Analyzer
Denis the SQL Menace
http://sqlservercode.blogspot.com/
MikeV06 wrote:
> If I understand what I am doing ...
> I can run an unnamed parameter query in Query Analyzer
> SELECT au_lname, au_fname
> FROM dbo.authors
> WHERE (state = ?)
> However, that is about all I can do with it. I have not been able to do
> anything with named parameter queries.
> I guess the Books Online is a little off on these?
> Is it possible to create some form of parameter query that accomplishes th
e
> same thing as they do in Access?
> Thanks.
> Mike.|||Adding to the post by "Menace" -- Don't expect SQL Server to prompt you to
enter the parameter value (in a dialog box like MS Access does). That
functionality is not built in to SQL Server. You would need to build your
own GUI for that...
Keith Kratochvil
"MikeV06" <me@.privacy.net> wrote in message
news:189zasiw6e1fr$.dlg@.mycomputer06.invalid.com...
> If I understand what I am doing ...
> I can run an unnamed parameter query in Query Analyzer
> SELECT au_lname, au_fname
> FROM dbo.authors
> WHERE (state = ?)
> However, that is about all I can do with it. I have not been able to do
> anything with named parameter queries.
> I guess the Books Online is a little off on these?
> Is it possible to create some form of parameter query that accomplishes
> the
> same thing as they do in Access?
> Thanks.
> Mike.|||On Wed, 31 May 2006 14:04:08 -0500, Keith Kratochvil wrote:

> Adding to the post by "Menace" -- Don't expect SQL Server to prompt you to
> enter the parameter value (in a dialog box like MS Access does). That
> functionality is not built in to SQL Server. You would need to build your
> own GUI for that...
Thanks to you both. I thought as much. It is a shame though. The Query
Designer will pop up a dialog box for filling in the values. Works with the
? unnamed one OK; has a problem with the named one.
I guess I will have to find another way.
Thanks again.

Parameter Queries

I am migrating a database from Access to SQL Server 2000. In Access, I had
several Parameter Queries set up so that users could specify a parameter
based on their need. I combed through Books on Line and figured out how to
do it in Query Designer. Is there a way to save this query with the set up
parameters into a view so that I can have other users re-use it?
thank you
See "Inline User-Defined Functions" in BOL.
AMB
"gmead7" wrote:

> I am migrating a database from Access to SQL Server 2000. In Access, I had
> several Parameter Queries set up so that users could specify a parameter
> based on their need. I combed through Books on Line and figured out how to
> do it in Query Designer. Is there a way to save this query with the set up
> parameters into a view so that I can have other users re-use it?
> thank you
|||A view acts like a table in SQL Server. E.G. "Select * from Table_View"
works if "Table_View" is a table or a view. There are no "parameters" that
you can specify for a view. If you want to have a query change based on
user input, I'd have to point you to stored procedures.
Scott
"gmead7" <gmead7@.discussions.microsoft.com> wrote in message
news:E6793343-5F16-4194-89D3-3A78F346971C@.microsoft.com...
>I am migrating a database from Access to SQL Server 2000. In Access, I had
> several Parameter Queries set up so that users could specify a parameter
> based on their need. I combed through Books on Line and figured out how
> to
> do it in Query Designer. Is there a way to save this query with the set
> up
> parameters into a view so that I can have other users re-use it?
> thank you

Parameter Queries

Is there a way in Transact-SQL to do a "runtime" parameter query within a MS
Access adp frontend? This is easily done in a pure MS Access mdb using the
square brackets, e.g. [ENTER Customer Number].Hi
SQL Server will not allow this, but you may want to ask in an access
newsgroup to find the best solution.
John
"Peter Marshall at OCC" wrote:

> Is there a way in Transact-SQL to do a "runtime" parameter query within a
MS
> Access adp frontend? This is easily done in a pure MS Access mdb using th
e
> square brackets, e.g. [ENTER Customer Number].
>
>|||If you call a SQL Server stored proc that takes parameters without passing a
ny MSAccess will contrive to prompt you for values. The
prompt will be the name of the parameter, with the window title of : "Enter
Parameter Value".
Good enough?
If you want to replace the prompt, you'll have to code
"Peter Marshall at OCC" <peter.marshall@.ohiocoatingscompany.com> wrote in message news:ubCn
sarvFHA.596@.TK2MSFTNGP12.phx.gbl...
> Is there a way in Transact-SQL to do a "runtime" parameter query within a
MS
> Access adp frontend? This is easily done in a pure MS Access mdb using th
e
> square brackets, e.g. [ENTER Customer Number].
>

Parameter Queries

I am migrating a database from Access to SQL Server 2000. In Access, I had
several Parameter Queries set up so that users could specify a parameter
based on their need. I combed through Books on Line and figured out how to
do it in Query Designer. Is there a way to save this query with the set up
parameters into a view so that I can have other users re-use it?
thank youSee "Inline User-Defined Functions" in BOL.
AMB
"gmead7" wrote:
> I am migrating a database from Access to SQL Server 2000. In Access, I had
> several Parameter Queries set up so that users could specify a parameter
> based on their need. I combed through Books on Line and figured out how to
> do it in Query Designer. Is there a way to save this query with the set up
> parameters into a view so that I can have other users re-use it?
> thank you|||A view acts like a table in SQL Server. E.G. "Select * from Table_View"
works if "Table_View" is a table or a view. There are no "parameters" that
you can specify for a view. If you want to have a query change based on
user input, I'd have to point you to stored procedures.
Scott
"gmead7" <gmead7@.discussions.microsoft.com> wrote in message
news:E6793343-5F16-4194-89D3-3A78F346971C@.microsoft.com...
>I am migrating a database from Access to SQL Server 2000. In Access, I had
> several Parameter Queries set up so that users could specify a parameter
> based on their need. I combed through Books on Line and figured out how
> to
> do it in Query Designer. Is there a way to save this query with the set
> up
> parameters into a view so that I can have other users re-use it?
> thank you

Parameter Queries

I am migrating a database from Access to SQL Server 2000. In Access, I had
several Parameter Queries set up so that users could specify a parameter
based on their need. I combed through Books on Line and figured out how to
do it in Query Designer. Is there a way to save this query with the set up
parameters into a view so that I can have other users re-use it?
thank youSee "Inline User-Defined Functions" in BOL.
AMB
"gmead7" wrote:

> I am migrating a database from Access to SQL Server 2000. In Access, I ha
d
> several Parameter Queries set up so that users could specify a parameter
> based on their need. I combed through Books on Line and figured out how t
o
> do it in Query Designer. Is there a way to save this query with the set u
p
> parameters into a view so that I can have other users re-use it?
> thank you|||A view acts like a table in SQL Server. E.G. "Select * from Table_View"
works if "Table_View" is a table or a view. There are no "parameters" that
you can specify for a view. If you want to have a query change based on
user input, I'd have to point you to stored procedures.
Scott
"gmead7" <gmead7@.discussions.microsoft.com> wrote in message
news:E6793343-5F16-4194-89D3-3A78F346971C@.microsoft.com...
>I am migrating a database from Access to SQL Server 2000. In Access, I had
> several Parameter Queries set up so that users could specify a parameter
> based on their need. I combed through Books on Line and figured out how
> to
> do it in Query Designer. Is there a way to save this query with the set
> up
> parameters into a view so that I can have other users re-use it?
> thank yousql

Monday, March 26, 2012

parameter is missing a value

I have a SQL Report in SQL 2005 that has 2 datasets. The second
dataset queries a value that is passed as a parameter to the first
dataset. It works well as the main report but when I use it as a
linked report and put a default on parameter field and hide the
prompt, it gives me "xxx parameter is missing a value". It seems that
it's not reading the parameter at all.
Now if I have the second dataset as Non-queried and embed the value
and label fields on the report itself, it works well even as a linked
report. However, I dont want to do this because these values and
labels change. I cannot embed it on each report (around 50 of them)
and change each report one by one if there is a change.
I appreciate your help in advance.
Ben
%$^*&I finally got the report to work. The first dataset didn't recognize
the 3-character default value because it expected a 10 character
string (as declared in the second dataset). I just added seven
trailing spaces without quotes and it worked fine.
Ben
On Nov 15, 11:57 am, benito <lochinvar...@.gmail.com> wrote:
> I have a SQL Report in SQL 2005 that has 2 datasets. Theseconddatasetqueriesavaluethat is passed as a parameter to the firstdataset. It works well as the main report but when I use it as a
> linked report and put a default on parameter field and hide the
> prompt, it gives me "xxx parameter is missing avalue". It seems that
> it's not reading the parameter at all.
> Now if I have theseconddatasetas Non-queried and embed thevalue
> and label fields on the report itself, it works well even as a linked
> report. However, I dont want to do this because these values and
> labels change. I cannot embed it on each report (around 50 of them)
> and change each report one by one if there is a change.
> I appreciate your help in advance.
> Ben
> %$^*&

Parameter Information cannot be derived from SQL statements with sub-select queries

Parameter Information cannot be derived from SQL statements with sub-select queries. Set Parameter information before preparing command.

Here's the query:

update GCDE_SEQ
set LAST_NO = (select max(FLD_NO)
from PONL_FLD)
,UPDT_USER = ?
,UPDT_DT = getdate()
where SEQ_NM = 'FLD_NO'

Why can't Execute SQL Task handle this simple query? I figure i can use 2 SQL Execute SQL Task, one to get the max into a var, and the other to do the updating. However, this is alot of trouble since i'm having this almost exact query in alot of places. Any way around this?

I guess you are using OLE DB connection. I have seen similar problems with Execute SQL task involving complex queries with parameters and OLE DB connection.

A simple work around is to set "ByPassPrepare" to true. Try this and let us know if it helped. Otherwise, I can investigate the issue more to suggest other work arounds.|||Kaarthik's answer is most likely the best way to go, however since you state that

Thanh Duong wrote:
i'm having this almost exact query in alot of places. Any way around this?

you may want to wrap this command in a Store Proc or View anyway. Then the parameters should work fine against the SP too.|||

Still failed with bypassprepare=true. I have a var mapped to parameter name 0 (like how i'm doing it with all other execute sql task). Any other solutions not involving sprocs?

Error: 0xC002F210 at Update Last Do_Seq_No, Execute SQL Task: Executing the query "update GCDE_SEQ

set LAST_NO =

(select max(CAST(OLD_DO_NO AS INT))

from DONL_DO_HDR)

,UPDT_USER = ?

,UPDT_DT = getdate()

where SEQ_NM = 'DO_SEQ_NO'" failed with the following error: "Parameter Information cannot be derived from SQL statements with sub-select queries. Set parameter information before preparing command.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

|||I could not repro your problem.

In "Execute SQL Task" I set the following properties:
ConnectionType=OLE DB
SQLStatement= update UserTable
set age = (select max(CAST(age AS INT)) from AgeTable)
,[name] = ?
,somedate = getdate()
where sex = 'M'
BypassPrepare=True

Parameter Mapping: Variable Name=nameVar, Direction=Input, Data Type=VARCHAR, Parameter Name=0

nameVar is a String variable.

I could successfully execute this task.

If I set BypassPrepare=False, the same task fails throwing the error message "[Execute SQL Task] Error: Executing the query "update UserTable
set age = (select max(CAST(age AS..." failed with the following error: "Parameter Information cannot be derived from SQL statements with sub-select queries. Set parameter information before preparing command.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

The tables I used for this taks are created as follows:

create table UserTable (
age int,
[name] varchar(10),
somedate datetime,
sex varchar(2)
)
create table AgeTable (
age varchar(5)
)|||THANKS FOR TRYING, ILL MESS AROUND WITH IT SOME MORE AND SEE WHAT I'M DOING WRONGsql

Parameter Information cannot be derived from SQL Statements with sub-select queries

I have the following query that finds the most Serial number of the
most current part manufactured at point 105 (near the end of the
process), then uses that serial number to find out when it started
production at point 39 (the front of the process). Then, it gives a
listing, summarized by part type (CCode), of everything in process from
the time that part entered the line, until it passes point 105. This
gives an accurate snapshot of everthing that is on the production line.
However, I need to modify this query so that the user can enter a start
date and time, and the query will find a part near that time (either
the next part, or the part just before), and produce the same results.
Here is my original query (It works with no issue):
SELECT CCode, COUNT(CCode) AS CCount
FROM [Broadcast] A
WHERE (ReportingPoint = '39') AND
(ProcessDate >= (SELECT W.ProcessDate
FROM [Broadcast] AS W JOIN
(SELECT TOP 1 ProcessDate, SerialNumber
FROM [Broadcast]
WHERE ReportingPoint = '105'
ORDER BY ProcessDate DESC) AS X ON
W.SerialNumber = X.SerialNumber AND
W.ProcessDate < X.ProcessDate
WHERE W.ReportingPoint = '39'))
GROUP BY CCode
ORDER BY CCode
I changed the subquery to get user entry as follows:
(SELECT SerialNumber
FROM [Broadcast]
WHERE ReportingPoint = '105' AND
ProcessDate >= @.GetDateFromUser
ORDER BY ProcessDate DESC) AS X ON
W.SerialNumber = X.SerialNumber AND
W.ProcessDate < X.ProcessDate
But I get the following error:
Parameter Information cannot be derived from SQL Statements with
sub-select queries. Set parameter information before preparing
command.
How can I get this infomation from the user before the sub-select query?Bump
Timothy.Rybak@.gmail.com wrote:
> I have the following query that finds the most Serial number of the
> most current part manufactured at point 105 (near the end of the
> process), then uses that serial number to find out when it started
> production at point 39 (the front of the process). Then, it gives a
> listing, summarized by part type (CCode), of everything in process from
> the time that part entered the line, until it passes point 105. This
> gives an accurate snapshot of everthing that is on the production line.
> However, I need to modify this query so that the user can enter a start
> date and time, and the query will find a part near that time (either
> the next part, or the part just before), and produce the same results.
> Here is my original query (It works with no issue):
> SELECT CCode, COUNT(CCode) AS CCount
> FROM [Broadcast] A
> WHERE (ReportingPoint = '39') AND
> (ProcessDate >=> (SELECT W.ProcessDate
> FROM [Broadcast] AS W JOIN
> (SELECT TOP 1 ProcessDate, SerialNumber
> FROM [Broadcast]
> WHERE ReportingPoint = '105'
> ORDER BY ProcessDate DESC) AS X ON
> W.SerialNumber = X.SerialNumber AND
> W.ProcessDate < X.ProcessDate
> WHERE W.ReportingPoint = '39'))
> GROUP BY CCode
> ORDER BY CCode
> I changed the subquery to get user entry as follows:
> (SELECT SerialNumber
> FROM [Broadcast]
> WHERE ReportingPoint = '105' AND
> ProcessDate >= @.GetDateFromUser
> ORDER BY ProcessDate DESC) AS X ON
> W.SerialNumber = X.SerialNumber AND
> W.ProcessDate < X.ProcessDate
> But I get the following error:
> Parameter Information cannot be derived from SQL Statements with
> sub-select queries. Set parameter information before preparing
> command.
> How can I get this infomation from the user before the sub-select query?

Friday, March 23, 2012

parameter and Oracle DB

I have a dataset that queries an Oracle DB. I am getting an error when I try
to set a parameter in the query. Basically I have the following:
WHERE x.system_num = s.system_num
and na.service_id = x.service_id
and na.location_num = sl.location_num
and sd.device_mux_id = dm2.device_mux_id
and (x.auth_date >= @.param_start)
ORDER BY m.mso_name, sl.system_name, system_type, d.device_address
The error I get is the following:
ORA-00936: missing expression
(System.Data.OracleClient)
When I remove the "and (x.auth_date >= @.param_start)" the error goes away.
The missing expression sounds like I have a comma at the end of the SELECT or
FROM line; but when the param line is removed the error goes away. So I
don't think that is it.
Could it be in how the parameter is set up? Just throwing out ideas here.
Thank you for any help offered.
Rob CuscadenFYI
After several HOURS of silly errors someone found the solution to this
problem. I wanted to post it here so that others can gain from my hours of
frustration. I will not say that it will work in ALL cases; but that it
works in mine and it is something for others to try if they are stuck.
Problem, using Reporting Services 2005 and accessing an Oracle database with
parameters.
In Reporting Services queries of a SQL DB, I have seen the @.param_startdate
used in the query and then the parameter is defined in the "Report
Parameters" option on the "Reports" top menu option.
So, for example
Select *
From sometable
Where sometable.datefield >= @.param_startdate
Then you go modify the Report Parameters option for the project.
If you try this with an ORACLE DB you will get several errors (ORA-00936:
missing expression is one of them).
SOLUTION: To solve this for an ORACLE DB, use the following:
SELECT *
FROM sometable
WHERE sometable.datefield = :param_startdate
-----
Now I am sure that this little ( : ) is documented somewhere in a document
out there somewhere. BUT for crying out loud... such a simple solution that
took 6 hours to find.
Please feel my frustration and I hope I prevent this from happening elsewhere.
Rob Cuscaden
"Rob" wrote:
> I have a dataset that queries an Oracle DB. I am getting an error when I try
> to set a parameter in the query. Basically I have the following:
> WHERE x.system_num = s.system_num
> and na.service_id = x.service_id
> and na.location_num = sl.location_num
> and sd.device_mux_id = dm2.device_mux_id
> and (x.auth_date >= @.param_start)
> ORDER BY m.mso_name, sl.system_name, system_type, d.device_address
> The error I get is the following:
> ORA-00936: missing expression
> (System.Data.OracleClient)
> When I remove the "and (x.auth_date >= @.param_start)" the error goes away.
> The missing expression sounds like I have a comma at the end of the SELECT or
> FROM line; but when the param line is removed the error goes away. So I
> don't think that is it.
> Could it be in how the parameter is set up? Just throwing out ideas here.
> Thank you for any help offered.
> Rob Cuscaden
>|||Rob,
I have been having the same problem and my queries look up an Oracle DB
also. I ended up getting so frustrated at mine, that our Oracle DB
Administrator told me to use a program called Toad for Oracle. Used that
which the SQL module in that program works very similar as the Microsoft
Query tool (Data, Import External Data,
New Database Query).
Anyway, I looked at the SQL Statement after building the Query (joining
tables, adding fields, adding criteria filters, etc). And I saw the same
Where table.field LIKE :whatever
Well, I tried using that in the Microsoft Query (SQL Statement) and I end up
getting some other error which I can't duplicate right now. I tried it again
and now it just completely bails me out of excel with the ever famous Debug,
Send Error Report, etc.
Anyway, wasn't sure if you ran into anything more you could share that might
help in my situation.
I also posted this issue somewhere else on here explaining more in detail
about how originally I created the query with one line of criteria where 3 of
my criteria having 5 "or" conditions. When I refresh or return data to Excel
it works fine.
Problem starts occurring if I ever have to go back in to the query to edit
and then try and refresh or return the data again. That's where I get the
ORA-00936 Missing expression error message.
I'm so stuck on this and really need to resolve somehow, because the data
that's returned from this query is used for charts, graphs, pivot tables,
that are all part of a giant VBA I wrote to automate dashboards for a bunch
of engineers.
Anwyay, any suggestions you or anyone might have on this would be greatly
appreciated.
"Rob" wrote:
> FYI
> After several HOURS of silly errors someone found the solution to this
> problem. I wanted to post it here so that others can gain from my hours of
> frustration. I will not say that it will work in ALL cases; but that it
> works in mine and it is something for others to try if they are stuck.
> Problem, using Reporting Services 2005 and accessing an Oracle database with
> parameters.
> In Reporting Services queries of a SQL DB, I have seen the @.param_startdate
> used in the query and then the parameter is defined in the "Report
> Parameters" option on the "Reports" top menu option.
> So, for example
> Select *
> From sometable
> Where sometable.datefield >= @.param_startdate
> Then you go modify the Report Parameters option for the project.
> If you try this with an ORACLE DB you will get several errors (ORA-00936:
> missing expression is one of them).
> SOLUTION: To solve this for an ORACLE DB, use the following:
> SELECT *
> FROM sometable
> WHERE sometable.datefield = :param_startdate
> -----
> Now I am sure that this little ( : ) is documented somewhere in a document
> out there somewhere. BUT for crying out loud... such a simple solution that
> took 6 hours to find.
> Please feel my frustration and I hope I prevent this from happening elsewhere.
> Rob Cuscaden
> "Rob" wrote:
> > I have a dataset that queries an Oracle DB. I am getting an error when I try
> > to set a parameter in the query. Basically I have the following:
> >
> > WHERE x.system_num = s.system_num
> > and na.service_id = x.service_id
> > and na.location_num = sl.location_num
> > and sd.device_mux_id = dm2.device_mux_id
> > and (x.auth_date >= @.param_start)
> >
> > ORDER BY m.mso_name, sl.system_name, system_type, d.device_address
> >
> > The error I get is the following:
> >
> > ORA-00936: missing expression
> > (System.Data.OracleClient)
> >
> > When I remove the "and (x.auth_date >= @.param_start)" the error goes away.
> > The missing expression sounds like I have a comma at the end of the SELECT or
> > FROM line; but when the param line is removed the error goes away. So I
> > don't think that is it.
> >
> > Could it be in how the parameter is set up? Just throwing out ideas here.
> >
> > Thank you for any help offered.
> > Rob Cuscaden
> >sql

Wednesday, March 21, 2012

Parallel Plans

Hi,
Our production server is a Compaq Proliant with 2 CPU. How can I determine
that my queries benefit from having 2 CPU? Are parallel plans automatically
created our I must configure server options?
Any help would be greatly appreciated.
LeilaYou can check if your server is set to use parallel query plans on the
processor tab of the server properties in Enterprise Manager.
You can check if any queries with a parallel plan are executed by tracing
the DegreeOfParallelism event with SQL Profiler. See the topic "Degree of
Parallelism" in Books Online for details.
Jacco Schalkwijk
SQL Server MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:OmMhaHYzEHA.1192@.tk2msftngp13.phx.gbl...
> Hi,
> Our production server is a Compaq Proliant with 2 CPU. How can I determine
> that my queries benefit from having 2 CPU? Are parallel plans
> automatically
> created our I must configure server options?
> Any help would be greatly appreciated.
> Leila
>|||If you are using the database with more than one user, then you are
likely to benefit from the extra CPU even if no parallellism is chosen,
because in that case the workload can be divided over 2 CPU's instead of
just the 1.
Gert-Jan
Leila wrote:
> Hi,
> Our production server is a Compaq Proliant with 2 CPU. How can I determine
> that my queries benefit from having 2 CPU? Are parallel plans automaticall
y
> created our I must configure server options?
> Any help would be greatly appreciated.
> Leila

Parallel Plans

Hi,
Our production server is a Compaq Proliant with 2 CPU. How can I determine
that my queries benefit from having 2 CPU? Are parallel plans automatically
created our I must configure server options?
Any help would be greatly appreciated.
Leila
You can check if your server is set to use parallel query plans on the
processor tab of the server properties in Enterprise Manager.
You can check if any queries with a parallel plan are executed by tracing
the DegreeOfParallelism event with SQL Profiler. See the topic "Degree of
Parallelism" in Books Online for details.
Jacco Schalkwijk
SQL Server MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:OmMhaHYzEHA.1192@.tk2msftngp13.phx.gbl...
> Hi,
> Our production server is a Compaq Proliant with 2 CPU. How can I determine
> that my queries benefit from having 2 CPU? Are parallel plans
> automatically
> created our I must configure server options?
> Any help would be greatly appreciated.
> Leila
>
|||If you are using the database with more than one user, then you are
likely to benefit from the extra CPU even if no parallellism is chosen,
because in that case the workload can be divided over 2 CPU's instead of
just the 1.
Gert-Jan
Leila wrote:
> Hi,
> Our production server is a Compaq Proliant with 2 CPU. How can I determine
> that my queries benefit from having 2 CPU? Are parallel plans automatically
> created our I must configure server options?
> Any help would be greatly appreciated.
> Leila

Parallel Plans

Hi,
Our production server is a Compaq Proliant with 2 CPU. How can I determine
that my queries benefit from having 2 CPU? Are parallel plans automatically
created our I must configure server options?
Any help would be greatly appreciated.
LeilaYou can check if your server is set to use parallel query plans on the
processor tab of the server properties in Enterprise Manager.
You can check if any queries with a parallel plan are executed by tracing
the DegreeOfParallelism event with SQL Profiler. See the topic "Degree of
Parallelism" in Books Online for details.
--
Jacco Schalkwijk
SQL Server MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:OmMhaHYzEHA.1192@.tk2msftngp13.phx.gbl...
> Hi,
> Our production server is a Compaq Proliant with 2 CPU. How can I determine
> that my queries benefit from having 2 CPU? Are parallel plans
> automatically
> created our I must configure server options?
> Any help would be greatly appreciated.
> Leila
>|||If you are using the database with more than one user, then you are
likely to benefit from the extra CPU even if no parallellism is chosen,
because in that case the workload can be divided over 2 CPU's instead of
just the 1.
Gert-Jan
Leila wrote:
> Hi,
> Our production server is a Compaq Proliant with 2 CPU. How can I determine
> that my queries benefit from having 2 CPU? Are parallel plans automatically
> created our I must configure server options?
> Any help would be greatly appreciated.
> Leilasql

Monday, March 12, 2012

Paging query bugs out when adding a where clause

I am getting incorrect results from my paging query, where the same results are being returned multiple times.

Here are two queries I have found to bring the same results:

select top 20 * from lookupdocuments_dbv where catname_cst='MyCategoryName' and (docid_cin not in (select top 620 docid_cin from lookupdocuments_dbv where catname_cst='MyCategoryName'))

select top 20 * from lookupdocuments_dbv where catname_cst='MyCategoryName' and (docid_cin not in (select top 640 docid_cin from lookupdocuments_dbv where catname_cst='MyCategoryName'))

When I remove the catname_cst where clause it brings back results properly (i.e. records 622-642 and 643-663).

What is wrong with my where clause that is causing identical data to be returned?

I'll try to be a bit more generic with my question.

How would you properly add a where clause in the T-SQL paging query technique I am using?

The template for the paging query I am using shows up in a few tutorials, it's a fairly known technique:

SELECT TOP rows_to_return * FROM table WHERE unique_id NOT IN (SELECT TOP row_to_start_at unique_id FROM table)

Basically it ignores the rows from Row 0 through row_to_start_at and from that starting point it selects rows until rows_to_return is reached.

So how should a where clause (i.e. where catname_cst='mycategoryname') be properly added to this paging query?

|||

Do a quick google search for "custom paging + stored procedure" and you will find some sample code on how to write custom paging which is much more efficient than what you are doing.

|||

You may be suggesting a tutorial (4guysfromrolla.com?) that uses temp tables and variables for current/last records. If you are, is creating a temp table (with potentially 40,000+ rows to be inserted) really more efficient than selecting the top X rows in my technique? I realize that my technique will become progressively slower as the Y (rows to be ignored) value increases, but I don't believe it should ever exceed the time required in creating a temp table that houses every single row.

I will test this out tomorrow and see how it works.

|||

There is no ORDER BY to go with your TOP so you will get just 20 rows in no particular order. You might as well use SET ROWCOUNT 20 which is faster than TOP 20. Also look into using EXISTS instead of IN. IN gets internally converted into OR's and may not result in efficient query plans.

|||

I will use SET ROWCOUNT, but I cannot determine how NOT EXISTS will help with filtering rows. The only result from google on the subject shares my confusion.

I'll test out the temp tables technique, but even if it works perfectly, I'll still want to know why my technique isn't working 100% of the time. Efficiency aside, there just doesn't seem to be anything wrong with the query, maybe it's the data.. the first dozen or so pages will look fine, then suddenly every so often an identical page is found, then they start coming in more frequently, and soon enough you're seeing more identical pages than non. This isn't just something I'm seeing on a site grid, I'm getting the same identical results within SQL Server. I think that based on the response and lack of responses it is not something with the query (as that kind of thing would have been pointed out, considering it should just be some simple logic error)..

The strange part is that if I perform a query such as getting the top 40 and the top 20 rows starting after results 620 and 640 respectively, 1-20 of each query will be the same, and 21-40 of the first query will contain the proper data that should be showing up in the second query..

To sum that up.. because I really want an answer:

Query 1 should get 40 results starting after result 620, so it should return 621-660.

Query 2 should get 20 results starting after result 640, so it should return 641-660.

Query 1 returns results 621-660.

Query 2 returns results 621-640.

|||

Could I get some insight into how I would write a paging query with not exists?

|||

The reason you are not getting the right results, as I mentioned above, is using TOP without ORDER BY. Its like saying "give me the top 20 records that match this criteria". There is every chance the same record may show up in the next set or an expected record may not show up at all. TOP X is incomplete by itself, although syntactically correct.

|||

Thanks, I implemented a version of my technique with Order By as you suggest and it works.

Wednesday, March 7, 2012

PAGEIOLATCH_SH performance problem

Hi,
We are currently experiencing huge performance problems when running stored
procs that consists of several 'select into' queries of type:
select A as AA,
B as BB,
sum(cast(C as decimal)) as CC
into myResult
from myTable
where id = 1 and C <> ' '
group by A, B
Database size is >200gig.
The query uses an index on (id, A, B)
The execution plan looks like this:
44% insert
28% bookmark lookup
28% index scan
The following blocking is reported:
SP10: myuser, sleeping, select into, PAGEIOLATCH_SH
SP6 : system, background, DBCC
SP10 (blocking) myResult (tab), myTable
SP6 (blocked by SP10) myResult (tab)
Can anyone explain what the system/dbcc task is doing and if this blocking
causes the problem?
I checked some of the counters but the only unusual value I found is a disk
io of < 2MB which I understand is too low.
Is there an easy way to improve this, would disk defrag or index reorg help?
Any help is much appreciated,
FrankThe fact that your query is blocking DBCC shouldn't be an issue. If it were
the other way around, you'd have a problem. That being said, the DBCC
commands are ussually part of the Database Maintenance tasks. You should be
running operations while the maintenance is going on. I'd look at the
schedule of these two.
The PAGEIOLATCH_SH wait happens, the question is what is the duration? If
the latches are taking too long, that could be indicative of server memory
starvation or a sub perfoming Disk I/O subsystem.
Look at these performance counters:
SQL Server:Memory Manager TargetServerMemory and TotalServerMemory: if the
Target is larger than the Total, SQL Server would be assisted by having more
memory available to it.
SQL Server:Latch AverageLatchWait, LatchWaitperSecond, and TotalLatchWait:
if the waits are larger than 100 ms, you probably have some sluggishness.
If they are greater than 1000 ms (1 second), then you may have a serious
issue. The wait count is a tough one to determine because it is highly
dependent on system resources and useage. However, if the wait times are
high and the wait counts are low, then each request is suffering.
Physical or Logical Disk: sec/Read and sec/Writ: these are the seek latency
counters. They tell you the responsiveness of the disk. For an optimal
disk I/O subsystem, these should be in the range of 10 to 15 ms per I/O, on
average. You might get some 100 ms or more per I/O, but should only be for
short durations.
Also take a look at the Disk %Read and %Write %Idle, this will tell you how
busy your disk are for the various I/O types. You'll want to check out the
Read/sec and Write/sec, most modern disks should be able to support up to
200 to 300 I/O operations per second. You'll aslo want to look at Read
Bytes/sec and Write Bytes/sec to see if you are overloading the disk
subsystem bandwidth.
Also look at all of the counters for the SQL Server:Buffer Manager. These
will tell you the distribution of the memory manager segments within the
Buffer Pool. Look to see if any area is being overused.
Hope this gives you some areas to look at.
Sincerely,
Anthony Thomas
"FrankM" <a@.b.c> wrote in message
news:%23KcYiYq4EHA.1404@.TK2MSFTNGP11.phx.gbl...
Hi,
We are currently experiencing huge performance problems when running stored
procs that consists of several 'select into' queries of type:
select A as AA,
B as BB,
sum(cast(C as decimal)) as CC
into myResult
from myTable
where id = 1 and C <> ' '
group by A, B
Database size is >200gig.
The query uses an index on (id, A, B)
The execution plan looks like this:
44% insert
28% bookmark lookup
28% index scan
The following blocking is reported:
SP10: myuser, sleeping, select into, PAGEIOLATCH_SH
SP6 : system, background, DBCC
SP10 (blocking) myResult (tab), myTable
SP6 (blocked by SP10) myResult (tab)
Can anyone explain what the system/dbcc task is doing and if this blocking
causes the problem?
I checked some of the counters but the only unusual value I found is a disk
io of < 2MB which I understand is too low.
Is there an easy way to improve this, would disk defrag or index reorg help?
Any help is much appreciated,
Frank|||Frank -- what is the wait resource being reported for the I/O latch in
question? I would be curious as to why a sleeping spid is persistently
waiting on an I/O to complete.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:uTDIYtq4EHA.3708@.TK2MSFTNGP14.phx.gbl...
> The fact that your query is blocking DBCC shouldn't be an issue. If it
> were
> the other way around, you'd have a problem. That being said, the DBCC
> commands are ussually part of the Database Maintenance tasks. You should
> be
> running operations while the maintenance is going on. I'd look at the
> schedule of these two.
> The PAGEIOLATCH_SH wait happens, the question is what is the duration? If
> the latches are taking too long, that could be indicative of server memory
> starvation or a sub perfoming Disk I/O subsystem.
> Look at these performance counters:
> SQL Server:Memory Manager TargetServerMemory and TotalServerMemory: if the
> Target is larger than the Total, SQL Server would be assisted by having
> more
> memory available to it.
> SQL Server:Latch AverageLatchWait, LatchWaitperSecond, and TotalLatchWait:
> if the waits are larger than 100 ms, you probably have some sluggishness.
> If they are greater than 1000 ms (1 second), then you may have a serious
> issue. The wait count is a tough one to determine because it is highly
> dependent on system resources and useage. However, if the wait times are
> high and the wait counts are low, then each request is suffering.
> Physical or Logical Disk: sec/Read and sec/Writ: these are the seek
> latency
> counters. They tell you the responsiveness of the disk. For an optimal
> disk I/O subsystem, these should be in the range of 10 to 15 ms per I/O,
> on
> average. You might get some 100 ms or more per I/O, but should only be
> for
> short durations.
> Also take a look at the Disk %Read and %Write %Idle, this will tell you
> how
> busy your disk are for the various I/O types. You'll want to check out
> the
> Read/sec and Write/sec, most modern disks should be able to support up to
> 200 to 300 I/O operations per second. You'll aslo want to look at Read
> Bytes/sec and Write Bytes/sec to see if you are overloading the disk
> subsystem bandwidth.
> Also look at all of the counters for the SQL Server:Buffer Manager. These
> will tell you the distribution of the memory manager segments within the
> Buffer Pool. Look to see if any area is being overused.
> Hope this gives you some areas to look at.
> Sincerely,
>
> Anthony Thomas
>
> --
> "FrankM" <a@.b.c> wrote in message
> news:%23KcYiYq4EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We are currently experiencing huge performance problems when running
> stored
> procs that consists of several 'select into' queries of type:
> select A as AA,
> B as BB,
> sum(cast(C as decimal)) as CC
> into myResult
> from myTable
> where id = 1 and C <> ' '
> group by A, B
>
> Database size is >200gig.
> The query uses an index on (id, A, B)
> The execution plan looks like this:
> 44% insert
> 28% bookmark lookup
> 28% index scan
>
> The following blocking is reported:
> SP10: myuser, sleeping, select into, PAGEIOLATCH_SH
> SP6 : system, background, DBCC
> SP10 (blocking) myResult (tab), myTable
> SP6 (blocked by SP10) myResult (tab)
>
> Can anyone explain what the system/dbcc task is doing and if this blocking
> causes the problem?
>
> I checked some of the counters but the only unusual value I found is a
> disk
> io of < 2MB which I understand is too low.
> Is there an easy way to improve this, would disk defrag or index reorg
> help?
>
> Any help is much appreciated,
> Frank
>|||Kevin, I believe it's the stored proc which runs in query analyzer.
"Kevin Stark" <SENDkevo97NO@.POTTEDhotMEATmailHERE.com> wrote in message
news:uqfcMdr4EHA.1192@.tk2msftngp13.phx.gbl...
> Frank -- what is the wait resource being reported for the I/O latch in
> question? I would be curious as to why a sleeping spid is persistently
> waiting on an I/O to complete.
>
> "AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
> news:uTDIYtq4EHA.3708@.TK2MSFTNGP14.phx.gbl...
> > The fact that your query is blocking DBCC shouldn't be an issue. If it
> > were
> > the other way around, you'd have a problem. That being said, the DBCC
> > commands are ussually part of the Database Maintenance tasks. You
should
> > be
> > running operations while the maintenance is going on. I'd look at the
> > schedule of these two.
> >
> > The PAGEIOLATCH_SH wait happens, the question is what is the duration?
If
> > the latches are taking too long, that could be indicative of server
memory
> > starvation or a sub perfoming Disk I/O subsystem.
> >
> > Look at these performance counters:
> >
> > SQL Server:Memory Manager TargetServerMemory and TotalServerMemory: if
the
> > Target is larger than the Total, SQL Server would be assisted by having
> > more
> > memory available to it.
> >
> > SQL Server:Latch AverageLatchWait, LatchWaitperSecond, and
TotalLatchWait:
> > if the waits are larger than 100 ms, you probably have some
sluggishness.
> > If they are greater than 1000 ms (1 second), then you may have a serious
> > issue. The wait count is a tough one to determine because it is highly
> > dependent on system resources and useage. However, if the wait times
are
> > high and the wait counts are low, then each request is suffering.
> >
> > Physical or Logical Disk: sec/Read and sec/Writ: these are the seek
> > latency
> > counters. They tell you the responsiveness of the disk. For an optimal
> > disk I/O subsystem, these should be in the range of 10 to 15 ms per I/O,
> > on
> > average. You might get some 100 ms or more per I/O, but should only be
> > for
> > short durations.
> >
> > Also take a look at the Disk %Read and %Write %Idle, this will tell you
> > how
> > busy your disk are for the various I/O types. You'll want to check out
> > the
> > Read/sec and Write/sec, most modern disks should be able to support up
to
> > 200 to 300 I/O operations per second. You'll aslo want to look at Read
> > Bytes/sec and Write Bytes/sec to see if you are overloading the disk
> > subsystem bandwidth.
> >
> > Also look at all of the counters for the SQL Server:Buffer Manager.
These
> > will tell you the distribution of the memory manager segments within the
> > Buffer Pool. Look to see if any area is being overused.
> >
> > Hope this gives you some areas to look at.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> >
> > --
> >
> > "FrankM" <a@.b.c> wrote in message
> > news:%23KcYiYq4EHA.1404@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > We are currently experiencing huge performance problems when running
> > stored
> > procs that consists of several 'select into' queries of type:
> >
> > select A as AA,
> > B as BB,
> > sum(cast(C as decimal)) as CC
> > into myResult
> > from myTable
> > where id = 1 and C <> ' '
> > group by A, B
> >
> >
> > Database size is >200gig.
> >
> > The query uses an index on (id, A, B)
> >
> > The execution plan looks like this:
> > 44% insert
> > 28% bookmark lookup
> > 28% index scan
> >
> >
> > The following blocking is reported:
> > SP10: myuser, sleeping, select into, PAGEIOLATCH_SH
> > SP6 : system, background, DBCC
> >
> > SP10 (blocking) myResult (tab), myTable
> > SP6 (blocked by SP10) myResult (tab)
> >
> >
> > Can anyone explain what the system/dbcc task is doing and if this
blocking
> > causes the problem?
> >
> >
> > I checked some of the counters but the only unusual value I found is a
> > disk
> > io of < 2MB which I understand is too low.
> >
> > Is there an easy way to improve this, would disk defrag or index reorg
> > help?
> >
> >
> > Any help is much appreciated,
> > Frank
> >
> >
>

PAGEIOLATCH_SH performance problem

Hi,
We are currently experiencing huge performance problems when running stored
procs that consists of several 'select into' queries of type:
select A as AA,
B as BB,
sum(cast(C as decimal)) as CC
into myResult
from myTable
where id = 1 and C <> ' '
group by A, B
Database size is >200gig.
The query uses an index on (id, A, B)
The execution plan looks like this:
44% insert
28% bookmark lookup
28% index scan
The following blocking is reported:
SP10: myuser, sleeping, select into, PAGEIOLATCH_SH
SP6 : system, background, DBCC
SP10 (blocking) myResult (tab), myTable
SP6 (blocked by SP10) myResult (tab)
Can anyone explain what the system/dbcc task is doing and if this blocking
causes the problem?
I checked some of the counters but the only unusual value I found is a disk
io of < 2MB which I understand is too low.
Is there an easy way to improve this, would disk defrag or index reorg help?
Any help is much appreciated,
Frank
The fact that your query is blocking DBCC shouldn't be an issue. If it were
the other way around, you'd have a problem. That being said, the DBCC
commands are ussually part of the Database Maintenance tasks. You should be
running operations while the maintenance is going on. I'd look at the
schedule of these two.
The PAGEIOLATCH_SH wait happens, the question is what is the duration? If
the latches are taking too long, that could be indicative of server memory
starvation or a sub perfoming Disk I/O subsystem.
Look at these performance counters:
SQL Server:Memory Manager TargetServerMemory and TotalServerMemory: if the
Target is larger than the Total, SQL Server would be assisted by having more
memory available to it.
SQL Server:Latch AverageLatchWait, LatchWaitperSecond, and TotalLatchWait:
if the waits are larger than 100 ms, you probably have some sluggishness.
If they are greater than 1000 ms (1 second), then you may have a serious
issue. The wait count is a tough one to determine because it is highly
dependent on system resources and useage. However, if the wait times are
high and the wait counts are low, then each request is suffering.
Physical or Logical Disk: sec/Read and sec/Writ: these are the seek latency
counters. They tell you the responsiveness of the disk. For an optimal
disk I/O subsystem, these should be in the range of 10 to 15 ms per I/O, on
average. You might get some 100 ms or more per I/O, but should only be for
short durations.
Also take a look at the Disk %Read and %Write %Idle, this will tell you how
busy your disk are for the various I/O types. You'll want to check out the
Read/sec and Write/sec, most modern disks should be able to support up to
200 to 300 I/O operations per second. You'll aslo want to look at Read
Bytes/sec and Write Bytes/sec to see if you are overloading the disk
subsystem bandwidth.
Also look at all of the counters for the SQL Server:Buffer Manager. These
will tell you the distribution of the memory manager segments within the
Buffer Pool. Look to see if any area is being overused.
Hope this gives you some areas to look at.
Sincerely,
Anthony Thomas

"FrankM" <a@.b.c> wrote in message
news:%23KcYiYq4EHA.1404@.TK2MSFTNGP11.phx.gbl...
Hi,
We are currently experiencing huge performance problems when running stored
procs that consists of several 'select into' queries of type:
select A as AA,
B as BB,
sum(cast(C as decimal)) as CC
into myResult
from myTable
where id = 1 and C <> ' '
group by A, B
Database size is >200gig.
The query uses an index on (id, A, B)
The execution plan looks like this:
44% insert
28% bookmark lookup
28% index scan
The following blocking is reported:
SP10: myuser, sleeping, select into, PAGEIOLATCH_SH
SP6 : system, background, DBCC
SP10 (blocking) myResult (tab), myTable
SP6 (blocked by SP10) myResult (tab)
Can anyone explain what the system/dbcc task is doing and if this blocking
causes the problem?
I checked some of the counters but the only unusual value I found is a disk
io of < 2MB which I understand is too low.
Is there an easy way to improve this, would disk defrag or index reorg help?
Any help is much appreciated,
Frank
|||Frank -- what is the wait resource being reported for the I/O latch in
question? I would be curious as to why a sleeping spid is persistently
waiting on an I/O to complete.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:uTDIYtq4EHA.3708@.TK2MSFTNGP14.phx.gbl...
> The fact that your query is blocking DBCC shouldn't be an issue. If it
> were
> the other way around, you'd have a problem. That being said, the DBCC
> commands are ussually part of the Database Maintenance tasks. You should
> be
> running operations while the maintenance is going on. I'd look at the
> schedule of these two.
> The PAGEIOLATCH_SH wait happens, the question is what is the duration? If
> the latches are taking too long, that could be indicative of server memory
> starvation or a sub perfoming Disk I/O subsystem.
> Look at these performance counters:
> SQL Server:Memory Manager TargetServerMemory and TotalServerMemory: if the
> Target is larger than the Total, SQL Server would be assisted by having
> more
> memory available to it.
> SQL Server:Latch AverageLatchWait, LatchWaitperSecond, and TotalLatchWait:
> if the waits are larger than 100 ms, you probably have some sluggishness.
> If they are greater than 1000 ms (1 second), then you may have a serious
> issue. The wait count is a tough one to determine because it is highly
> dependent on system resources and useage. However, if the wait times are
> high and the wait counts are low, then each request is suffering.
> Physical or Logical Disk: sec/Read and sec/Writ: these are the seek
> latency
> counters. They tell you the responsiveness of the disk. For an optimal
> disk I/O subsystem, these should be in the range of 10 to 15 ms per I/O,
> on
> average. You might get some 100 ms or more per I/O, but should only be
> for
> short durations.
> Also take a look at the Disk %Read and %Write %Idle, this will tell you
> how
> busy your disk are for the various I/O types. You'll want to check out
> the
> Read/sec and Write/sec, most modern disks should be able to support up to
> 200 to 300 I/O operations per second. You'll aslo want to look at Read
> Bytes/sec and Write Bytes/sec to see if you are overloading the disk
> subsystem bandwidth.
> Also look at all of the counters for the SQL Server:Buffer Manager. These
> will tell you the distribution of the memory manager segments within the
> Buffer Pool. Look to see if any area is being overused.
> Hope this gives you some areas to look at.
> Sincerely,
>
> Anthony Thomas
>
> --
> "FrankM" <a@.b.c> wrote in message
> news:%23KcYiYq4EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We are currently experiencing huge performance problems when running
> stored
> procs that consists of several 'select into' queries of type:
> select A as AA,
> B as BB,
> sum(cast(C as decimal)) as CC
> into myResult
> from myTable
> where id = 1 and C <> ' '
> group by A, B
>
> Database size is >200gig.
> The query uses an index on (id, A, B)
> The execution plan looks like this:
> 44% insert
> 28% bookmark lookup
> 28% index scan
>
> The following blocking is reported:
> SP10: myuser, sleeping, select into, PAGEIOLATCH_SH
> SP6 : system, background, DBCC
> SP10 (blocking) myResult (tab), myTable
> SP6 (blocked by SP10) myResult (tab)
>
> Can anyone explain what the system/dbcc task is doing and if this blocking
> causes the problem?
>
> I checked some of the counters but the only unusual value I found is a
> disk
> io of < 2MB which I understand is too low.
> Is there an easy way to improve this, would disk defrag or index reorg
> help?
>
> Any help is much appreciated,
> Frank
>
|||Kevin, I believe it's the stored proc which runs in query analyzer.
"Kevin Stark" <SENDkevo97NO@.POTTEDhotMEATmailHERE.com> wrote in message
news:uqfcMdr4EHA.1192@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Frank -- what is the wait resource being reported for the I/O latch in
> question? I would be curious as to why a sleeping spid is persistently
> waiting on an I/O to complete.
>
> "AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
> news:uTDIYtq4EHA.3708@.TK2MSFTNGP14.phx.gbl...
should[vbcol=seagreen]
If[vbcol=seagreen]
memory[vbcol=seagreen]
the[vbcol=seagreen]
TotalLatchWait:[vbcol=seagreen]
sluggishness.[vbcol=seagreen]
are[vbcol=seagreen]
to[vbcol=seagreen]
These[vbcol=seagreen]
blocking
>

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.
>

Friday, March 30, 2012

Parameter Queries in Analyser

Parameter Queries in Access Projects

Parameter Queries

Parameter Queries

Parameter Queries

Parameter Queries

Parameter Queries

Monday, March 26, 2012

parameter is missing a value

Parameter Information cannot be derived from SQL statements with sub-select queries

Parameter Information cannot be derived from SQL Statements with sub-select queries

Friday, March 23, 2012

parameter and Oracle DB

Wednesday, March 21, 2012

Parallel Plans

Parallel Plans

Parallel Plans

Monday, March 12, 2012

Paging query bugs out when adding a where clause

Wednesday, March 7, 2012

PAGEIOLATCH_SH performance problem

PAGEIOLATCH_SH performance problem

pageiolatch_sh

pageiolatch_sh

Page Numbers in Excel

Blog Archive

About Me