Behavior question about updateable resultsets...

I have a question regarding a certain behavior of updateable
resultsets. If I update a column using any of the updateXXX methods and
then try to use the getXXX methods from the same column to see if it
updated the results locally and not on the server, I get the same old
value. I have to call updateRow() but that updates the underlying
database and still gives me the old value until I execute the same
query again and get a new resultset. Maybe the code below will clarify
my question more..
Connection con = null;
Statement stmt;
ResultSet rst;
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
System.out.println("Getting connection.");
con = DriverManager.getConnection(url);
System.out.println("Connection successful.");
String st = "select age,sname,snum FROM student;";
stmt =
con.createStatement(ResultSet.TYPE_SCROLL_SENSITIV E,ResultSet.CONCUR_UPDATABLE);
rst = stmt.executeQuery(st);
rst.last();
System.out.print(rst.getInt(1)+" ");
System.out.print(rst.getString(2)+" ");
System.out.print(rst.getLong(3)+"\n");
rst.updateInt(1,23);
rst.updateRow();
System.out.print(rst.getInt(1)+" ");
System.out.print(rst.getString(2)+" ");
System.out.print(rst.getLong(3)+"\n");
The output is:
Getting connection.
Connection successful.
25 Edward Baker 578875478
25 Edward Baker 578875478
If I were the run the same code again, I get:
Getting connection.
Connection successful.
23 Edward Baker 578875478
23 Edward Baker 578875478
Any/all help is appreciated
Thanks
Devansh Dhutia
University of Iowa
This is a bug and it does not have a trivial fix. I would like to encourage
you to file this using the product feedback website (below).
The problem here is that there are two mutually exclusive places where
column values transit through the driver. The first, used only by getters,
is through the columns array (lives on the statement). The second, used
only by setters, is through the colParam array (also lives on the
statement). The columns array is read only the colParam array is write
only...
Note that the JDBC spec provides (in section 27.1.22, p. 718 - 719 JDBC API
Tutorial and Reference, Third edition, (Fisher, Ellis, Bruce)) that a result
set's own updates need not be visible to it. Obviously we would not like
this to be the default behavior but it is going to take a lot of work and it
would help to have customer feedback that clarified why this behavior should
be changed.
Entering a bug:
Go to http://lab.msdn.microsoft.com/produc...k/default.aspx
Product/Technology:
SQL Server 2005
Category:
JDBC Driver
Make sure to add JDBC SqlServer 2005 to the but title.
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/
<devansh.dhutia@.gmail.com> wrote in message
news:1128188361.813553.300740@.g47g2000cwa.googlegr oups.com...
>I have a question regarding a certain behavior of updateable
> resultsets. If I update a column using any of the updateXXX methods and
> then try to use the getXXX methods from the same column to see if it
> updated the results locally and not on the server, I get the same old
> value. I have to call updateRow() but that updates the underlying
> database and still gives me the old value until I execute the same
> query again and get a new resultset. Maybe the code below will clarify
> my question more..
> Connection con = null;
> Statement stmt;
> ResultSet rst;
> Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
> System.out.println("Getting connection.");
> con = DriverManager.getConnection(url);
> System.out.println("Connection successful.");
> String st = "select age,sname,snum FROM student;";
> stmt =
> con.createStatement(ResultSet.TYPE_SCROLL_SENSITIV E,ResultSet.CONCUR_UPDATABLE);
> rst = stmt.executeQuery(st);
> rst.last();
> System.out.print(rst.getInt(1)+" ");
> System.out.print(rst.getString(2)+" ");
> System.out.print(rst.getLong(3)+"\n");
> rst.updateInt(1,23);
> rst.updateRow();
> System.out.print(rst.getInt(1)+" ");
> System.out.print(rst.getString(2)+" ");
> System.out.print(rst.getLong(3)+"\n");
> The output is:
> Getting connection.
> Connection successful.
> 25 Edward Baker 578875478
> 25 Edward Baker 578875478
> If I were the run the same code again, I get:
> Getting connection.
> Connection successful.
> 23 Edward Baker 578875478
> 23 Edward Baker 578875478
> Any/all help is appreciated
> Thanks
> Devansh Dhutia
> University of Iowa
>

behavior of ssis packages running under SSMS manually vs. job agent automatically

I have a ssis package that has multiple large lookups without memory restriction. When running the package manually from SSMS on the same server it runs on when running automatically under the job agent, the package errors out when the server memory gets depleted by the loading of the large lookup reference data. One of the messages I get is

"An out-of-memory condition prevented the creation of the buffer object. "

Anyway, the package runs successfully when it runs automatically under the job agent.

I was curious as to why the above happens. Is that a bug or is the run time behavior different under these 2 environments by design.

js40

If you are running through SSMS, you are likley executing the package via your machine, not the server. Do you have the full SSIS client installed? (Do you have a SQL Server Integration Service service when looking at "Services" under the control panel?)|||

No, i ran the package on the same server as where it runs automatically under sql agent. I started it manually from SMSS under remote desktop. The server is sql server 2005 (64 bit) with SP1 and 8gig memory.

Actually, several times it crashed SMSS and the last thing I saw just before the SMSS window just plain disappeared was some errors related to memory.

Below are different errors i got when i ran this package serveral times.

1) The Data Flow task failed to create a required thread and cannot begin running. The usually occurs when there is an out-of-memory state.

2)The system reports 99 percent memory load. There are 8588873728 bytes of physical memory with 67133440 bytes free. There are 2147352576 bytes of virtual memory with 27369472 bytes free. The paging file has 12954959872 bytes with 4205600768 bytes free.

3)A buffer failed while allocating 2048 bytes.

4)An out-of-memory condition prevented the creation of the buffer object.

js40

Behavior of SSIS lookup transform on full cache setting with low computer memory

I would like to know what happens when a very large reference data set for a lookup transform with full caching enabled is getting loaded during package execution and the computer memory runs out or is very low.

Does SSIS

a) give an out of memory error of some sort

b) resort to a no caching or partial caching mode

c) maintain the full caching mode but will switch to using the paging file(virtual memory).

I think it will resort to using the page file in which case the benefits of in memory lookups are lost and performance would suffer. If I cannot upgrade the memory or shrink the reference set somehow, i should switch that lookup task to use partial caching or no caching with an indexed lookup table. Would this make sense?

It won't do B unless you explicitly set it. I think it does C as part of the standard Windows memory management, but you might see A too if you are dealing with large data sets.

Your approach would make sense. I'd start by making sure that you are only caching the bare minimum reference set needed, though.

Behavior of SQL Server on table scans

Hi All,

if someone can point me to documentation on this I would appreciate it.....

If there isn't any....

I am wondering about the behavior of SQL Server for table scans. In other databases tables scans are not really table scans, they are scans of the underlying tablespace for all the rows that are in the table.....and if many tables are placed into the same tablespace then the obvious slowdown occurs as rows are scanned that are not in the table.

This used to be the case in server 7......but is it still the case in 2005 that if the explain says 'table scan' it will in fact scan the filegroup the table is in?

Some other databases also have a map of the row numbers and the table it is in and the optimiser decides whether to scan the data itself or to navigate through the map and fetch a row at a time depending on the stats....

It seems that the grahical explain does not tell me more than 'table scan'. Is there any way to see down to the physical level of what the optimiser is going to do?

Thank You in Advance

Peter

www.peternolan.com

SQL Server will only scan the pages that belong to the table being scanned, though read ahead may be issued that causes some amount of data from other tables to be read in as well depending on the scan. Those pages will not actually be looked at.

|||

Hi Peter,

thanks for your reply.....is there any good document to read on this? I am happy to do my 'homework'.

I'd be interested to know how it can only read pages with rows from the table as my understanding is that when tables cohabit a file group, such as primary, that rows from different tables can co-habit the same pages and therefore I would think it will read at least every page on which a row from that table is present.

We are testing to see if we can see any performance differences by placing large tables in their own file groups.

As a broader comment/question.....and I guess to other people here too....I am not sure if it is just me not able to find details on the optimiser and how it is working in a BI environment or if there is just not a great level of detail around. I've read all that people have pointed to but I still feel 'short on hard information' as to what the optimiser does.

We are working on building up documentation for our team on the optimiser and how it works and we have cut/paste all we can find.....but we are still struggling to influence the optimiser....are we missing something? Is there any one web page or any one document out of MSFT that is the 'bible' on optimsing dimensional models on SQL Server? I would have thought that by now someone would have sat down and written the definitive guide on large dimensional models and the 2005 optimser.

As background.....

I have been doing BI for 15 years and done many projects on DB2, Oracle, Sybase IQ as well as a few others. one on SQL Server 7 some 7 years ago. I used to be something of an expert with optimisers........We are doing our first large scale testing on a new product we have developed which has a dimensional model for the DW at it's base. We have read presentations on 'What We learned in the first year' which references very large DWs on SQL Server. We have also read details of Project Real....so we read about all these 'large scale DWs' on SQL Server 2005 but I am surprised at not being able to find more information about influencing the optimiser or the setting up of file groups, indexes etc for a dimensional model.....hence my posts here and hence us looking around.....

So anyone who can point us to more information as to how the optimiser works, the assistance would be greatly appreciated..

Best Regards

Peter

Behavior of SQL Server on table scans

Hi All,

if someone can point me to documentation on this I would appreciate it.....

If there isn't any....

I am wondering about the behavior of SQL Server for table scans. In other databases tables scans are not really table scans, they are scans of the underlying tablespace for all the rows that are in the table.....and if many tables are placed into the same tablespace then the obvious slowdown occurs as rows are scanned that are not in the table.

This used to be the case in server 7......but is it still the case in 2005 that if the explain says 'table scan' it will in fact scan the filegroup the table is in?

Some other databases also have a map of the row numbers and the table it is in and the optimiser decides whether to scan the data itself or to navigate through the map and fetch a row at a time depending on the stats....

It seems that the grahical explain does not tell me more than 'table scan'. Is there any way to see down to the physical level of what the optimiser is going to do?

Thank You in Advance

Peter

www.peternolan.com

SQL Server will only scan the pages that belong to the table being scanned, though read ahead may be issued that causes some amount of data from other tables to be read in as well depending on the scan. Those pages will not actually be looked at.

|||

Hi Peter,

thanks for your reply.....is there any good document to read on this? I am happy to do my 'homework'.

I'd be interested to know how it can only read pages with rows from the table as my understanding is that when tables cohabit a file group, such as primary, that rows from different tables can co-habit the same pages and therefore I would think it will read at least every page on which a row from that table is present.

We are testing to see if we can see any performance differences by placing large tables in their own file groups.

As a broader comment/question.....and I guess to other people here too....I am not sure if it is just me not able to find details on the optimiser and how it is working in a BI environment or if there is just not a great level of detail around. I've read all that people have pointed to but I still feel 'short on hard information' as to what the optimiser does.

We are working on building up documentation for our team on the optimiser and how it works and we have cut/paste all we can find.....but we are still struggling to influence the optimiser....are we missing something? Is there any one web page or any one document out of MSFT that is the 'bible' on optimsing dimensional models on SQL Server? I would have thought that by now someone would have sat down and written the definitive guide on large dimensional models and the 2005 optimser.

As background.....

I have been doing BI for 15 years and done many projects on DB2, Oracle, Sybase IQ as well as a few others. one on SQL Server 7 some 7 years ago. I used to be something of an expert with optimisers........We are doing our first large scale testing on a new product we have developed which has a dimensional model for the DW at it's base. We have read presentations on 'What We learned in the first year' which references very large DWs on SQL Server. We have also read details of Project Real....so we read about all these 'large scale DWs' on SQL Server 2005 but I am surprised at not being able to find more information about influencing the optimiser or the setting up of file groups, indexes etc for a dimensional model.....hence my posts here and hence us looking around.....

So anyone who can point us to more information as to how the optimiser works, the assistance would be greatly appreciated..

Best Regards

Peter

behavior of SQL on joined queries

Hi all,

Currently our product has a setup that stores information about
transactions in a transaction table. Additionally, certain transactions
pertain to specific people, and extra information is stored in another
table. So for good or ill, things look like this right now:

create table TransactionHistory (
TrnID int identity (1,1),
TrnDT datetime,
--other information about a basic transaction goes here.
--All transactions have this info
Primary Key Clustered (TrnID)
)

Create Index TrnDTIndex on TransactionHistory(TrnDT)

create table PersonTransactionHistory (
TrnID int,
PersonID int,
--extended data pertaining only to "person" transactions goes
--here. only Person transactions have this
Primary Key Clustered(TrnID),
Foreign Key (TrnID) references TransactionHistory (TrnID)
)

Create Index TrnPersonIDIndex on PersonTransactionHistory(Person)

A query about a group of people over a certain date range might fetch
information like so:

select * from TransactionHistory TH
inner join PersonTransactionHistory PTH
on TH.TrnID = PTH.TrnID
where PTH.PersonID in some criteria
and TH.TrnDT between some date and some date

In my experience, this poses a real problem when trying to run queries
that uses both date and personID criteria. If my guesses are correct this
is because SQL is forced to do one of two things:

1 - Use TrnPersonIDIndex to find all transactions which match the person
criteria, then for each do a lookup in the PersonTransactionHistory to
fetch the TrnID and subsequently do a lookup of the TrnID in the clustered
index of the TransactionHistory Table, and finally determine if a given
transaction also matches the date time criteria.

2 - Use TrnDTIndex to final all transaction matching the date criteria,
and then perform lookups similar to the above, except for personID instead
of datetime.

Compounding this is my suspicion (based on performance comparison of when
I specify which indexes to use in the query vs when I let SQL Server
decide itself) that SQL sometimes chooses a very non optimal course. (Of
course, sometimes it chooses a better course than me - the point is I want
it to always be able to pick a good enough course such that I don't have
to bother specifying). Perhaps the table layout is making it difficult for
SQL Server to find a good query plan in all cases.

Basically I'm trying to determine ways to improve our table design here to
make reporting easier, as this gets painful when running report for
large groups of people during large date ranges. I see a few options based
on my above hypothesis, and am looking for comments and/or corrections.

1 - Add the TrnDT column to the PersonTransactionHistory Table as
well. Then create a foreign key relationship of PersonTransactionHistory
(TrnID, TrnDT) references TransactionHistory (TrnID, TrnDT) and create
indexes on PersonTransactionHistory with (TrnDT, PersonID) and
(PersonID, TrnDT). This seems like it would let SQL Server make
much more efficient execution plans. However, I am unsure if SQL server
can leverage the FK on TrnDT to use those new indexes if I give it a query
like:

select * from TransactionHistory TH
inner join PersonTransactionHistory PTH
on TH.TrnID = PTH.TrnID
where PTH.PersonID in some criteria
and TH.TrnDT between some date and some date

The trick being that SQL server would know that it can use PTH.TrnDT and
TH.TrnDT interchangably because of the foreign key (this would support all
the preexisting existing queries that explicitly named TH.TrnDT - any that
didn't explicitly specify the table would now have ambigious column
names...)

2 - Just coalesce the two tables into one. The original intent was to save
space by not requiring extra columns about Persons for all rows, many of
which did not have anything to do with a particular person (for instance a
contact point going active). In my experience with our product, the end
user's decisions about archiving and purging have a much bigger impact
than this, so in my opinion efficient querying is more important than
space. However I'm not sure if this is an elegant solution either. It also
might require more changes to existing code, although the use of views
might help.

We also run reports based on other criteria (columns I replaced with
comments above) but none of them are as problematic as the situation
above. However, it seems that if I can understand the best way to solve
this, I will be able to leverage that approach if other types of reports
become problematic.

Any opinions would be greatly appreciated. Also any references to good
sources regarding table and index design would be helpful as well (online
or offline references...)

thanks,
DaveMetal Dave (metal@.spam.spam) writes:
> create table TransactionHistory (
> TrnID int identity (1,1),
> TrnDT datetime,
> --other information about a basic transaction goes here.
> --All transactions have this info
> Primary Key Clustered (TrnID)
> )
> Create Index TrnDTIndex on TransactionHistory(TrnDT)
> create table PersonTransactionHistory (
> TrnID int,
> PersonID int,
> --extended data pertaining only to "person" transactions goes
> --here. only Person transactions have this
> Primary Key Clustered(TrnID),
> Foreign Key (TrnID) references TransactionHistory (TrnID)
> )
> Create Index TrnPersonIDIndex on PersonTransactionHistory(Person)

Given your query, it could be a good idea to have the clustered index
on TrnDT and PersonID instead. The main problem now with the queries
is that SQL Server will have to make a choice between Index Seek +
Bookmark Lookup on the one hand, and Clustered Index Scan on the other.
This is a guessing game that does not always end up the best way.

Of course, you may have other queries that are best off with clustering
on the Pkey, but this does not seem likely. (Insertion may however
benefit from a montonically increasing index. A clustered index on
PersonID may cause fragmentation.)

> 1 - Add the TrnDT column to the PersonTransactionHistory Table as
> well. Then create a foreign key relationship of PersonTransactionHistory
> (TrnID, TrnDT) references TransactionHistory (TrnID, TrnDT) and create
> indexes on PersonTransactionHistory with (TrnDT, PersonID) and
> (PersonID, TrnDT). This seems like it would let SQL Server make
> much more efficient execution plans. However, I am unsure if SQL server
> can leverage the FK on TrnDT to use those new indexes if I give it a query
> like:
> select * from TransactionHistory TH
> inner join PersonTransactionHistory PTH
> on TH.TrnID = PTH.TrnID
> where PTH.PersonID in some criteria
> and TH.TrnDT between some date and some date

Well, take a copy of the database and try it!

(But first try changing the clustered index.)

> 2 - Just coalesce the two tables into one. The original intent was to save
> space by not requiring extra columns about Persons for all rows, many of
> which did not have anything to do with a particular person (for instance a
> contact point going active).

Depends a little on the ration. If the PersonTransactionHistory is 50%
of all rows in the main table, collapsing into one is probably the best.
If it's 5%, I don't think it is.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 26 Oct 2004, Erland Sommarskog wrote:

> Given your query, it could be a good idea to have the clustered index
> on TrnDT and PersonID instead. The main problem now with the queries
> is that SQL Server will have to make a choice between Index Seek +
> Bookmark Lookup on the one hand, and Clustered Index Scan on the other.
> This is a guessing game that does not always end up the best way.
> Of course, you may have other queries that are best off with clustering
> on the Pkey, but this does not seem likely. (Insertion may however
> benefit from a montonically increasing index. A clustered index on
> PersonID may cause fragmentation.)

My intuition agrees with you regarding the index in this case. I'm
pretty sure the clustered bookmark scan kills us on many reports. However
I haven't looked with enough depth at the wide variety of queries we use
to know for sure where I should put the clustered index so I'm reserving
judgement for now. I'd also like to study a bit more first so that I
don't replace one hasty decision with another - it might solve ad
individual problem but exacerbate others.

For instance, I think

select * from PersonTransactionHistory PTH
inner join TransactionHistory TH on PTH.TrnID = TH.TrnID
where PTH.PersonID = 12345

would be harmed by moving the TH clustered index from TH.TrnID to
TH.TrnDT, as it would now have to make the same lookup vs scan choice in
order to perform the join. Does that make sound reasonable? And since it's
rare for us to access PTH without the inner join to TH, there are probably
many queries like this.

> > 1 - Add the TrnDT column to the PersonTransactionHistory Table as
> > well. Then create a foreign key relationship of PersonTransactionHistory
> > (TrnID, TrnDT) references TransactionHistory (TrnID, TrnDT) and create
> > indexes on PersonTransactionHistory with (TrnDT, PersonID) and
> > (PersonID, TrnDT). This seems like it would let SQL Server make
> > much more efficient execution plans. However, I am unsure if SQL server
> > can leverage the FK on TrnDT to use those new indexes if I give it a query
> > like:
> > select * from TransactionHistory TH
> > inner join PersonTransactionHistory PTH
> > on TH.TrnID = PTH.TrnID
> > where PTH.PersonID in some criteria
> > and TH.TrnDT between some date and some date
> Well, take a copy of the database and try it!

I appreciate the value of experimentation and normally would do that but
if it didn't work that wouldn't necesarily prove to me that I wasn't
simply doing something wrong like not making the foreign key specific
enough or putting something in my query which made SQL server ignore this
potential valuable relationship. So I was basically wondering if there
were any good docs regarding what types of information SQL Server will and
will no leverage in its choices or whether someone familiar with those
rules had some feedback off the top of their head.

> > 2 - Just coalesce the two tables into one. The original intent was to save
> > space by not requiring extra columns about Persons for all rows, many of
> > which did not have anything to do with a particular person (for instance a
> > contact point going active).
> Depends a little on the ration. If the PersonTransactionHistory is 50%
> of all rows in the main table, collapsing into one is probably the best.
> If it's 5%, I don't think it is.

It's probably between 20% and 40% depending on the particular
installation. It's your rationale that for 50% the space saved is
negligible whereas for 5% is is not? For me it's as more about limiting
the changes to the client software (definitely keeping the tables
separate) vs speeding up queries (possible coalescing) rather than a space
consideration. I did a test once and recall discovering we took up nearly
as much or more space with our indexes than our tables anyway, so
coalescing might make a big space difference anyway. (This amount of index
space suprised me but I'm not sure if there is a good rule of thumb for
how much space indexes should take.)

Rereading the post I probably should have just asked for good table design
references right up front. Any takers?

Thanks for the feedback.

Dave|||Metal Dave (metal@.spam.spam) writes:
> For instance, I think
> select * from PersonTransactionHistory PTH
> inner join TransactionHistory TH on PTH.TrnID = TH.TrnID
> where PTH.PersonID = 12345
> would be harmed by moving the TH clustered index from TH.TrnID to
> TH.TrnDT, as it would now have to make the same lookup vs scan choice in
> order to perform the join. Does that make sound reasonable? And since it's
> rare for us to access PTH without the inner join to TH, there are probably
> many queries like this.

Let's assume for the example that the clustered index in FTH is on PersonID.
Then the join against TH on TrnID will be akin to Index Seek + Bookmark
Lookup, no matter if the index on TrnID is clustered or not. In both
cases you would expect a plan with a Nested Loop join which means that
for each in FTH you look up a row in TH. The only difference if the index
on TrnID is non-clustered, is that you will get a few more reads for
each access. Which indeed is not neglible, since it multiplies with the
number of rows for PersonID.

And just like "SELECT * FROM tbl WHERE nonclusteredcol = @.val" has a
choice between index seek and scan, so have this query. Rather than
nested loop, the optimizer could go for hash or merge join which would
mean a single scan of TH. I would guess that the probability for this is
somewhat higher with a NC index on TrnID.

Of course, you opt to change only FTH, if you like.

> I appreciate the value of experimentation and normally would do that but
> if it didn't work that wouldn't necesarily prove to me that I wasn't
> simply doing something wrong like not making the foreign key specific
> enough or putting something in my query which made SQL server ignore this
> potential valuable relationship. So I was basically wondering if there
> were any good docs regarding what types of information SQL Server will and
> will no leverage in its choices or whether someone familiar with those
> rules had some feedback off the top of their head.

SQL Server does look at constraints, but really how intelligent it is,
I have not dug into. Thus, my encouragement of experimentation.

> It's probably between 20% and 40% depending on the particular
> installation. It's your rationale that for 50% the space saved is
> negligible whereas for 5% is is not?

Actually, I was more thinking in terms of performance, but space and
performance are related. My idea was that with 50%, the space saved is not
worth the extra complexity, and performance may suffer. With 5%, you save a
lot of space, since FTH would be a small table.

Your concern of having to change the client is certainly not one to be
neglected, and if this is costly in development time, I don't think it's
worth it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Behavior of OPTION

we have migrated code from SQL7 to SQL2K and in some stored procs OPTION
clause used as : OPTION (KEEPFIXED PLAN, FORCE ORDER)
now there is a complaint that these procs are running quite slow. while
trying to figure out the problem for the sluggishness
of the procedure, i noticed that commenting this line produced different
plan while uncommenting this line produced quite a
different one. Also, the usage of indexes varied drastically between these
plans (commented v/s uncommented OPTION clause)
can anybody throw some light on why using or NOT using OPTION would change
the execution plan?
thx in advanceHi
I prefer to remove hints when upgrading significant versions as things like
changes to the Query Optimizer may mean you are not using specific features
and as you have found they may be slower.
It is also a good idea to benchmark the system before and after the upgrade
to make sure that it is performing better!
It also usually a good time to review the code for general "good practices"
such as declaring temporary tables a the start of a procedure, reducing the
use of unnecessary temporary tables, owner prefixes for tables and stored
procedures, correct error and transaction handling etc...
Make sure that indexes and statistics are in place and rebuilt.
You can then look at the code and the query plans for the slower procedures.
John
"paraa" wrote:

> we have migrated code from SQL7 to SQL2K and in some stored procs OPTION
> clause used as : OPTION (KEEPFIXED PLAN, FORCE ORDER)
> now there is a complaint that these procs are running quite slow. while
> trying to figure out the problem for the sluggishness
> of the procedure, i noticed that commenting this line produced different
> plan while uncommenting this line produced quite a
> different one. Also, the usage of indexes varied drastically between these
> plans (commented v/s uncommented OPTION clause)
> can anybody throw some light on why using or NOT using OPTION would change
> the execution plan?
> thx in advance
>|||parasda
Firstly , BOL has a pretty good explanation about all options that you can
use
Actually ,FORCE ORDER option specifies that the join order indicated by the
query syntax is preserved during query optimization. In fact , query
optimizer is free to decide in what order (join) ( believe me, it is smart
enough) to execute the query im more efficient way ,so by using this option
you limit the optimizer to create an efficient execution plan
"paraa" <paraa@.discussions.microsoft.com> wrote in message
news:12F9AE7A-C7F4-4228-88C1-C1C5E35FA47B@.microsoft.com...
> we have migrated code from SQL7 to SQL2K and in some stored procs OPTION
> clause used as : OPTION (KEEPFIXED PLAN, FORCE ORDER)
> now there is a complaint that these procs are running quite slow. while
> trying to figure out the problem for the sluggishness
> of the procedure, i noticed that commenting this line produced different
> plan while uncommenting this line produced quite a
> different one. Also, the usage of indexes varied drastically between these
> plans (commented v/s uncommented OPTION clause)
> can anybody throw some light on why using or NOT using OPTION would change
> the execution plan?
> thx in advance
>

behavior of command in the 'SQL Query Analyzer'

Hello,
A command in 'SQL Query Analyzer does not return expected results.
This command:
sp_depends 'lkpRate'
Returns these results:
dbo.usp_Rate_delstored procedure
dbo.usp_Rate_insstored procedure
dbo.usp_Rate_updstored procedure
But fails to return:
dbo.usp_Rate_sel
This command:
sp_depends 'usp_rate_sel'
Returns this result:
Object does not reference any object, and no objects reference it.
Here is the table 'lkpRate':
\\CREATE TABLE [lkpRate] (
[pkRateId] [smallint] IDENTITY (1, 1) NOT NULL ,
[fkRateTypeId] [smallint] NOT NULL ,
[RateDescription] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Switch1] [tinyint] NOT NULL CONSTRAINT [DF_lkpRate_switch1] DEFAULT
(0),
[Rate] [smallmoney] NOT NULL ,
[rOrd] [tinyint] NOT NULL CONSTRAINT [DF_tblStartupAsst_saOrd] DEFAULT
(0),
[rHide] [bit] NOT NULL CONSTRAINT [DF_tblStartupAsst_saHide] DEFAULT
(0),
CONSTRAINT [PK_tblStartupAsst] PRIMARY KEY CLUSTERED
(
[pkRateId]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_lkpRate_lkpRateType] FOREIGN KEY
(
[fkRateTypeId]
) REFERENCES [lkpRateType] (
[pkRateTypeId]
) NOT FOR REPLICATION
) ON [PRIMARY]
GO
//
Here is the stored procedure:
\\
CREATE PROCEDURE dbo.usp_Rate_sel
AS
SET NOCOUNT ON;
SELECT
pkRateId,
fkRateTypeId,
RateDescription,
Switch1,
Rate,
rOrd,
rHide
FROM dbo.lkpRate
GO
//
What do you make of it that Query Analyzer doesn't see the stored
procedure as belonging to the table?
Thank you,
dbuchanan
Dependency information is maintained correctly only when objects are
(re)created in correct dependency order. If usp_rate_sel was created before
the table or if the table was later recreated, dependency info will be
incomplete. You can fix correct the dependency information by recreating
usp_rate_sel.
Hope this helps.
Dan Guzman
SQL Server MVP
"dbuchanan" <dbuchanan52@.hotmail.com> wrote in message
news:1141047719.180447.206490@.i40g2000cwc.googlegr oups.com...
> Hello,
> A command in 'SQL Query Analyzer does not return expected results.
> This command:
> sp_depends 'lkpRate'
> Returns these results:
> dbo.usp_Rate_del stored procedure
> dbo.usp_Rate_ins stored procedure
> dbo.usp_Rate_upd stored procedure
> But fails to return:
> dbo.usp_Rate_sel
> This command:
> sp_depends 'usp_rate_sel'
> Returns this result:
> Object does not reference any object, and no objects reference it.
> Here is the table 'lkpRate':
> \\CREATE TABLE [lkpRate] (
> [pkRateId] [smallint] IDENTITY (1, 1) NOT NULL ,
> [fkRateTypeId] [smallint] NOT NULL ,
> [RateDescription] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Switch1] [tinyint] NOT NULL CONSTRAINT [DF_lkpRate_switch1] DEFAULT
> (0),
> [Rate] [smallmoney] NOT NULL ,
> [rOrd] [tinyint] NOT NULL CONSTRAINT [DF_tblStartupAsst_saOrd] DEFAULT
> (0),
> [rHide] [bit] NOT NULL CONSTRAINT [DF_tblStartupAsst_saHide] DEFAULT
> (0),
> CONSTRAINT [PK_tblStartupAsst] PRIMARY KEY CLUSTERED
> (
> [pkRateId]
> ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
> CONSTRAINT [FK_lkpRate_lkpRateType] FOREIGN KEY
> (
> [fkRateTypeId]
> ) REFERENCES [lkpRateType] (
> [pkRateTypeId]
> ) NOT FOR REPLICATION
> ) ON [PRIMARY]
> GO
> //
> Here is the stored procedure:
> \\
> CREATE PROCEDURE dbo.usp_Rate_sel
> AS
> SET NOCOUNT ON;
> SELECT
> pkRateId,
> fkRateTypeId,
> RateDescription,
> Switch1,
> Rate,
> rOrd,
> rHide
> FROM dbo.lkpRate
> GO
> //
> What do you make of it that Query Analyzer doesn't see the stored
> procedure as belonging to the table?
> Thank you,
> dbuchanan
>
|||Dan
Thank you.
Is there any way, maybe some command that I can use, to identify those
objects that are not up to date?
'sp_depends' seems kind of worthless if information must be accounted
for 'manually' in order for the commands to work.
dbuchanan
|||dbuchanan (dbuchanan52@.hotmail.com) writes:
> Is there any way, maybe some command that I can use, to identify those
> objects that are not up to date?
Not really. You could run a SELECT on sysobjects to identify procedures
that have been created before tables, but that will probably give you
too much information.

> 'sp_depends' seems kind of worthless if information must be accounted
> for 'manually' in order for the commands to work.
Yes, it is a feature or limited use. I use it quite a bit myself though,
but what I do is that I build an empty database with our build tools, so
that I know that dependencies from tables to procedures are correct.
(Dependencies from procedures are not.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

behavior of command in the 'SQL Query Analyzer'

Hello,
A command in 'SQL Query Analyzer does not return expected results.
This command:
sp_depends 'lkpRate'
Returns these results:
dbo.usp_Rate_del stored procedure
dbo.usp_Rate_ins stored procedure
dbo.usp_Rate_upd stored procedure
But fails to return:
dbo.usp_Rate_sel
This command:
sp_depends 'usp_rate_sel'
Returns this result:
Object does not reference any object, and no objects reference it.
Here is the table 'lkpRate':
\\CREATE TABLE [lkpRate] (
[pkRateId] [smallint] IDENTITY (1, 1) NOT NULL ,
[fkRateTypeId] [smallint] NOT NULL ,
[RateDescription] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Switch1] [tinyint] NOT NULL CONSTRAINT [DF_lkpRate_switch1] DEFAULT
(0),
[Rate] [smallmoney] NOT NULL ,
[rOrd] [tinyint] NOT NULL CONSTRAINT [DF_tblStartupAsst_saOrd] DEFAULT
(0),
[rHide] [bit] NOT NULL CONSTRAINT [DF_tblStartupAsst_saHide] DEFAULT
(0),
CONSTRAINT [PK_tblStartupAsst] PRIMARY KEY CLUSTERED
(
[pkRateId]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_lkpRate_lkpRateType] FOREIGN KEY
(
[fkRateTypeId]
) REFERENCES [lkpRateType] (
[pkRateTypeId]
) NOT FOR REPLICATION
) ON [PRIMARY]
GO
//
Here is the stored procedure:
\\
CREATE PROCEDURE dbo.usp_Rate_sel
AS
SET NOCOUNT ON;
SELECT
pkRateId,
fkRateTypeId,
RateDescription,
Switch1,
Rate,
rOrd,
rHide
FROM dbo.lkpRate
GO
//
What do you make of it that Query Analyzer doesn't see the stored
procedure as belonging to the table?
Thank you,
dbuchananDependency information is maintained correctly only when objects are
(re)created in correct dependency order. If usp_rate_sel was created before
the table or if the table was later recreated, dependency info will be
incomplete. You can fix correct the dependency information by recreating
usp_rate_sel.
Hope this helps.
Dan Guzman
SQL Server MVP
"dbuchanan" <dbuchanan52@.hotmail.com> wrote in message
news:1141047719.180447.206490@.i40g2000cwc.googlegroups.com...
> Hello,
> A command in 'SQL Query Analyzer does not return expected results.
> This command:
> sp_depends 'lkpRate'
> Returns these results:
> dbo.usp_Rate_del stored procedure
> dbo.usp_Rate_ins stored procedure
> dbo.usp_Rate_upd stored procedure
> But fails to return:
> dbo.usp_Rate_sel
> This command:
> sp_depends 'usp_rate_sel'
> Returns this result:
> Object does not reference any object, and no objects reference it.
> Here is the table 'lkpRate':
> \\CREATE TABLE [lkpRate] (
> [pkRateId] [smallint] IDENTITY (1, 1) NOT NULL ,
> [fkRateTypeId] [smallint] NOT NULL ,
> [RateDescription] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Switch1] [tinyint] NOT NULL CONSTRAINT [DF_lkpRate_switch1] DEFAULT
> (0),
> [Rate] [smallmoney] NOT NULL ,
> [rOrd] [tinyint] NOT NULL CONSTRAINT [DF_tblStartupAsst_saOrd] DEFAULT
> (0),
> [rHide] [bit] NOT NULL CONSTRAINT [DF_tblStartupAsst_saHide] DEFAULT
> (0),
> CONSTRAINT [PK_tblStartupAsst] PRIMARY KEY CLUSTERED
> (
> [pkRateId]
> ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
> CONSTRAINT [FK_lkpRate_lkpRateType] FOREIGN KEY
> (
> [fkRateTypeId]
> ) REFERENCES [lkpRateType] (
> [pkRateTypeId]
> ) NOT FOR REPLICATION
> ) ON [PRIMARY]
> GO
> //
> Here is the stored procedure:
> \\
> CREATE PROCEDURE dbo.usp_Rate_sel
> AS
> SET NOCOUNT ON;
> SELECT
> pkRateId,
> fkRateTypeId,
> RateDescription,
> Switch1,
> Rate,
> rOrd,
> rHide
> FROM dbo.lkpRate
> GO
> //
> What do you make of it that Query Analyzer doesn't see the stored
> procedure as belonging to the table?
> Thank you,
> dbuchanan
>|||Dan
Thank you.
Is there any way, maybe some command that I can use, to identify those
objects that are not up to date?
'sp_depends' seems kind of worthless if information must be accounted
for 'manually' in order for the commands to work.
dbuchanan|||dbuchanan (dbuchanan52@.hotmail.com) writes:
> Is there any way, maybe some command that I can use, to identify those
> objects that are not up to date?
Not really. You could run a SELECT on sysobjects to identify procedures
that have been created before tables, but that will probably give you
too much information.

> 'sp_depends' seems kind of worthless if information must be accounted
> for 'manually' in order for the commands to work.
Yes, it is a feature or limited use. I use it quite a bit myself though,
but what I do is that I build an empty database with our build tools, so
that I know that dependencies from tables to procedures are correct.
(Dependencies from procedures are not.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Start by generating a script file in Enterprise Manager. Include all
stored procedures and views. Script as CREATE, but do NOT include the
DELETE!
Now edit the script, and change all the CREATE PROC and CREATE VIEW to
ALTER commands.
Then run the script. Run it a few times, if you want. I believe the
dependencies should be up to date.
(Personally I don't pay any attention to those dependencies, but that
is partly because I formed my habits before they introduced ALTER!)
Roy
On 27 Feb 2006 07:22:50 -0800, "dbuchanan" <dbuchanan52@.hotmail.com>
wrote:

>Dan
>Thank you.
>Is there any way, maybe some command that I can use, to identify those
>objects that are not up to date?
>'sp_depends' seems kind of worthless if information must be accounted
>for 'manually' in order for the commands to work.
>dbuchanan

behavior change in SSRS2005? bring up new IE window at a drillthrough action

Hi,
I just noticed a behavior change from SSRS 2000 (SP2 version) to SSRS
2005 (RTM version).
With SSRS 2000, if I use IE to bring up a report that can drillthrough
to other report, I can right-click at the textbox (where drillthrough
action would take place) to select "Open New Window". It will bring up
the drillthrough report in ANOTHER IE window along with the original
report in the original IE window.
Now with SSRS2005, if I do the same thing, right-click at the textbox
(where drillthrough would take place) does not give me "Open New
Window" selection any more.
Of course, in both cases, a simple click at the textbox would jump to
the drillthrough report which REPLACES the original report in the IE
window. But we really would like to see both reports at the same time.
To reproduce, one can try the sample AdventureWorks reports, open up
"Employee Sales Summary", select December 2003, Syed Abbas, view
report, then try right-click at order number SO58915. The followings
are the actual url (from right-click -> Properties) at the textbox for
drillthrough action.
SSRS2000:
http://kong-dell-4600/Reports$SONATA/Pages/Report.aspx?ServerUrl=http%3a%2f%2fkong-dell-4600%2fReportServer%24SONATA%3f%252fSampleReports%252fSales%2bOrder%2bDetail%26SalesOrderNumber%3dSO5421%26rs%253aParameterLanguage%3d%26rc%253aParameters%3dCollapsed%26rc%253aReplacementRoot%3dhttp%253a%252f%252fkong-dell-4600%252fReports%2524SONATA%252fPages%252fReport.aspx%253fServerUrl%253d
SSRS2005:
http://kong-dell-4600/Reports$MINUET/Reserved.ReportViewerWebControl.axd?ReportSession=nj1154fxuqxsxfn5rwz4wb55&ControlID=a97df556-62d2-4614-a898-ce42b4c28881&Culture=1033&UICulture=9&ReportStack=1&OpType=ReportArea&Controller=ClientControllerctl161&PageNumber=1&ZoomMode=Percent&ZoomPct=100&ReloadDocMap=true&EnableFindNext=False&LinkTarget=_top
Is there any way to bring back the SSRS2000 behavior in SSRS2005?
Any pointers or hints are welcome. Thanks.
KongKong,
Can you use the HTML Device Setting of LinkTarget to open all your
links in a new window? This would eliminate the need to right click at
all.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_soapapi_dev_3i49.asp
Andy Potter|||Hi, Andy,
Thanks for this suggestion.
I think if I write my own application then controlling HTML device
settings as you suggested would be easier.
All of my users are using report manager/report server from IE
directly. So I have to find a way to set LinkTarget to _blank (which
becomes default) in order to create a new window. Based on BOL, I
thought I could modify rsreportserver.config to specify LinkTarget
under Render ... HTML extension. Unfortunately the URL at the textbox
in SSRS 2005 is still http://...&LinkTarget=_top.
Any idea how to set LinkTarget to _blank as default in report
manager/report server config file?
Thanks.
Kong
Potter wrote:
> Kong,
> Can you use the HTML Device Setting of LinkTarget to open all your
> links in a new window? This would eliminate the need to right click at
> all.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_soapapi_dev_3i49.asp
> Andy Potter|||Kong,
You can change your navigation to use a 'Jump to URL' and do some
javascript:window.open(blah) expression in the jump to URL. Search the
group, there are a few examples.
Andy Potter|||Here is an example for CSV format. Just remove the rs:Format and rc:Encoding
parts if you are doing HTML (HTML is the default). The
Globals!ReportServerURL allows you to not hardcode the name of your server.
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
If you don't want to have it appear in a new window then do this in jump to
URL:
=Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<likong@.email.com> wrote in message
news:1137023689.498483.253560@.g14g2000cwa.googlegroups.com...
> Hi, Andy,
> Thanks for this suggestion.
> I think if I write my own application then controlling HTML device
> settings as you suggested would be easier.
> All of my users are using report manager/report server from IE
> directly. So I have to find a way to set LinkTarget to _blank (which
> becomes default) in order to create a new window. Based on BOL, I
> thought I could modify rsreportserver.config to specify LinkTarget
> under Render ... HTML extension. Unfortunately the URL at the textbox
> in SSRS 2005 is still http://...&LinkTarget=_top.
> Any idea how to set LinkTarget to _blank as default in report
> manager/report server config file?
> Thanks.
> Kong
>
> Potter wrote:
>> Kong,
>> Can you use the HTML Device Setting of LinkTarget to open all your
>> links in a new window? This would eliminate the need to right click at
>> all.
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_soapapi_dev_3i49.asp
>> Andy Potter
>|||Andy and Bruce, thanks for the great suggestion. It works perfectly.
Thanks.
Kong
Bruce L-C [MVP] wrote:
> Here is an example for CSV format. Just remove the rs:Format and rc:Encoding
> parts if you are doing HTML (HTML is the default). The
> Globals!ReportServerURL allows you to not hardcode the name of your server.
> Here is an example of a Jump to URL link I use. This causes Excel to come up
> with the data in a separate window:
> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> If you don't want to have it appear in a new window then do this in jump to
> URL:
> =Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
> Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <likong@.email.com> wrote in message
> news:1137023689.498483.253560@.g14g2000cwa.googlegroups.com...
> > Hi, Andy,
> >
> > Thanks for this suggestion.
> >
> > I think if I write my own application then controlling HTML device
> > settings as you suggested would be easier.
> >
> > All of my users are using report manager/report server from IE
> > directly. So I have to find a way to set LinkTarget to _blank (which
> > becomes default) in order to create a new window. Based on BOL, I
> > thought I could modify rsreportserver.config to specify LinkTarget
> > under Render ... HTML extension. Unfortunately the URL at the textbox
> > in SSRS 2005 is still http://...&LinkTarget=_top.
> >
> > Any idea how to set LinkTarget to _blank as default in report
> > manager/report server config file?
> >
> > Thanks.
> >
> > Kong
> >
> >
> > Potter wrote:
> >> Kong,
> >>
> >> Can you use the HTML Device Setting of LinkTarget to open all your
> >> links in a new window? This would eliminate the need to right click at
> >> all.
> >>
> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_soapapi_dev_3i49.asp
> >>
> >> Andy Potter
> >