Change SQL Server Query Optimizer Locking
Dec 23, 2004
Hi,
Our app has been distributed on more then 300 different sites.
On one of the sites we get the error "Could not continue scan with NOLOCK due to data movement" indicating that the query optimizer takes a NOLOCK for our select statement ( has been opened with adOpenDynamic, adLockOptimistic ).
It's no option to change the source, we have to solve this without touching the code.
Is there any way to tweak the query optimizer so that our app works correctly?
I know that there will be a reduction of performance but it's our only choose.
thnx in advance,
adOpenDynamic, adLockOptimistic
View 4 Replies
ADVERTISEMENT
Feb 7, 2008
Hi,
Can some one please send me the Sql Server 2005 QUERY OPTIMIZER artilce links?
Thanks
View 1 Replies
View Related
Jul 12, 2001
Hello,
I have a SQL command which I run on two separate servers. Both servers and configured and built the same. On server 1 it takes mere seconds, but on server 2 it takes over 5 minutes.
I have checked the execution plan on both servers and they are completely different. I ran UPDATE STATISTICS WITH FULLSCAN on both servers, but the execution plans were still different.
My question is why are the execution plans so different and how do I get them to execute with the same plan.
Thanks,
Chris.
View 1 Replies
View Related
Apr 22, 2004
I'm looking for an in depth book, article, faq, whatever, regarding the query optimizer...
I've read the books online pretty thoroughly and have been sql coding for a number of years. The system I work on relies heavily on real time access to data and the number crunching procedures we use are a critical part of the design. For the most part, sometimes through trial and error, I have been able to find ways to achieve the performance we need, but I'm often surprised by the methods that prove most effective.
For example, I have cases where I can only get the performance I'm looking for using table functions, and other cases where indexed temporary tables are the only way. I have statements that run fast as a select statement, but when converted to an update statement limp along, forcing me to resort to cursors, temp tables, or table hints with varying degrees of success.
I'm wondering if anyone has come across material that takes an in depth look at the various technologies available and how to tweek queries. I want to get away from hours of testing and hacking.
Thanks for reading. All replies are appreciated.
View 3 Replies
View Related
Jul 6, 1999
I am running a query showplan on a query. Can someone tell me how
to read what I am seeing? Or where I can get this information.
Thanks in advance,
Dianne
View 2 Replies
View Related
Feb 5, 2008
One quick question.
Is query optimizer a tool in sql that optimizes your queries? If so how do you use it?
Thanks
View 1 Replies
View Related
Jun 27, 2007
I am trying to resolve performance issues in a third party application. I have run the profiler and found a transaction that performs a table scan against a 6 million row table. This transaction occurs repeatedly, so I thought, just add an index on the columns in the where clause used here. After adding the index, I looked at the estimated execution plan in Query analyzer, and I find that it is still performing the table scan. If I run the query it takes over 60 seconds to run, if i add an index hint, it runs in under a second. I ran DBCC SHOW_STATISTICS to see if the statistics were up to date:
Statistics for INDEX 'IX_Finish_dept'.
Updated Rows Rows Sampled Steps Density Average key length
-------------------- -------------------- -------------------- ------ ------------------------ ------------------------
Jun 26 2007 5:18PM 6832336 6832336 150 2.1415579E-7 18.0
(1 row(s) affected)
All density Average Length Columns
------------------------ ------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2.1875491E-7 8.0 finish
1.9796084E-7 18.0 finish, dept
(2 row(s) affected)
RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
------------------------------------------------------ ------------------------ ------------------------ -------------------- ------------------------
1900-01-01 00:00:00.000 0.0 106110.0 0 0.0
2001-02-01 17:00:00.000 54121.0 47.0 22951 2.3581107
2001-02-28 17:00:00.000 44436.0 22.0 18121 2.4520473
2001-04-06 00:00:00.000 56830.0 76.0 24902 2.2820544
2001-08-10 17:00:00.000 196491.0 19.0 88800 2.2127116
2001-09-02 17:00:00.000 33070.0 50.0 15289 2.162993
2001-10-05 17:04:59.997 57975.0 30.0 22882 2.5335402
2001-11-05 15:31:59.997 50178.0 21.0 20899 2.4008613
2001-12-10 17:00:00.000 55266.0 38.0 25114 2.2006052
2002-01-03 17:00:00.000 40322.0 51.0 18649 2.1620376
2002-02-25 17:00:00.000 86338.0 24.0 39266 2.1987979
2002-08-15 06:11:00.000 296085.0 166.0 124526 2.3776772
2002-10-07 21:18:59.997 88727.0 826.0 39017 2.2740018
2002-12-17 16:59:00.000 127671.0 6.0 53314 2.3946545
2003-01-16 07:15:00.000 62206.0 71.0 24604 2.5281854
2003-01-21 07:15:00.000 8287.0 43.0 3661 2.2629712
2003-01-27 07:15:00.000 10402.0 68.0 4265 2.4389215
2003-01-31 07:15:00.000 9127.0 73.0 3784 2.4113607
2003-02-05 00:00:00.000 8362.0 327.0 3500 2.3891428
2003-02-10 00:00:00.000 8846.0 262.0 3230 2.7386997
2003-02-14 00:00:00.000 10018.0 51.0 4107 2.4386563
2003-02-20 00:00:00.000 10388.0 91.0 4686 2.2168159
2003-02-26 00:00:00.000 10571.0 69.0 4330 2.4407759
2003-03-03 00:00:00.000 10476.0 261.0 4423 2.3679929
2003-03-06 00:00:00.000 8858.0 594.0 3183 2.7829092
2003-04-02 00:00:00.000 57681.0 275.0 38622 1.4934366
2003-04-05 00:00:00.000 10539.0 29.0 8776 1.2008888
2003-04-09 00:00:00.000 9880.0 1324.0 7193 1.3735576
2003-04-12 00:00:00.000 8953.0 195.0 7737 1.1571668
2003-04-16 00:00:00.000 8385.0 177.0 7154 1.1719078
2003-04-21 00:00:00.000 8920.0 173.0 7756 1.1500773
2003-04-24 00:00:00.000 8563.0 156.0 7320 1.169649
2003-04-29 00:00:00.000 8462.0 137.0 7414 1.1412003
2003-05-02 00:00:00.000 9625.0 140.0 8363 1.1509027
2003-05-06 00:00:00.000 8208.0 904.0 6557 1.251792
2003-05-09 00:00:00.000 9211.0 119.0 7986 1.1533934
2003-05-19 00:00:00.000 19623.0 123.0 17290 1.1348679
2003-05-22 00:00:00.000 9568.0 246.0 8357 1.1449084
2003-05-28 00:00:00.000 9599.0 169.0 8553 1.1221651
2003-06-02 00:00:00.000 10937.0 174.0 9599 1.1393895
2003-07-11 00:00:00.000 99592.0 999.0 83573 1.1916767
2003-07-29 00:00:00.000 42434.0 111.0 33918 1.2510761
2003-08-21 00:00:00.000 59580.0 323.0 50756 1.1738282
2003-09-12 00:00:00.000 51779.0 1407.0 44298 1.1688789
2003-09-25 00:00:00.000 30655.0 255.0 26924 1.138533
2003-10-12 00:00:00.000 44573.0 968.0 37746 1.1808668
2003-10-28 00:00:00.000 38358.0 532.0 32689 1.1734222
2003-11-11 00:00:00.000 35158.0 145.0 28124 1.2500622
2003-12-04 00:00:00.000 61304.0 787.0 52882 1.1592383
2003-12-18 00:00:00.000 44462.0 221.0 39493 1.1257913
2004-01-06 00:00:00.000 56617.0 998.0 49471 1.1444252
2004-02-04 00:00:00.000 96694.0 537.0 83182 1.162425
2004-03-05 00:00:00.000 90850.0 716.0 78693 1.1544864
2004-03-23 00:00:00.000 48969.0 125.0 43450 1.1270195
2004-07-05 00:00:00.000 301725.0 1405.0 258824 1.1657491
2004-08-06 00:00:00.000 95079.0 1419.0 75445 1.2602259
2004-09-03 00:00:00.000 88056.0 193.0 68403 1.2873119
2004-09-23 01:30:12.997 57515.0 8.0 42891 1.3409261
2004-10-11 00:00:00.000 57204.0 116.0 40241 1.4215
2004-10-15 00:00:00.000 17702.0 186.0 12774 1.3856752
2004-10-19 00:00:00.000 9556.0 125.0 7305 1.3079661
2004-10-21 00:00:00.000 8898.0 133.0 6299 1.4126052
2004-10-25 00:00:00.000 8878.0 104.0 6372 1.3930645
2004-10-27 00:00:00.000 11904.0 252.0 6056 1.9656539
2004-10-29 00:00:00.000 8866.0 99.0 6551 1.3533812
2004-11-02 15:22:47.997 12287.0 1.0 9791 1.2547998
2004-11-05 13:16:50.997 12287.0 1.0 10013 1.2269822
2004-11-09 23:52:48.000 12284.0 4.0 9200 1.3352174
2004-11-12 17:17:59.997 12287.0 1.0 9360 1.3127136
2004-11-22 06:58:06.997 24575.0 1.0 19742 1.244745
2004-11-25 01:57:00.000 12287.0 1.0 8822 1.392768
2004-11-30 21:34:59.997 12287.0 1.0 9128 1.3459306
2004-12-03 13:21:24.000 12287.0 1.0 9085 1.3523003
2004-12-07 04:05:21.000 12285.0 5.0 9488 1.2947934
2004-12-09 13:25:00.000 12285.0 5.0 8993 1.3659106
2004-12-13 07:21:46.000 12282.0 10.0 9461 1.2981714
2004-12-15 18:41:23.000 12287.0 2.0 9112 1.3482937
2005-02-04 14:41:36.997 178768.0 58.0 133439 1.3396883
2005-02-23 00:00:00.000 51107.0 29.0 38624 1.3231586
2005-03-10 23:06:17.997 50891.0 24.0 38479 1.3225312
2005-03-28 00:00:00.000 45509.0 32.0 34203 1.3305169
2005-04-13 09:50:34.000 58778.0 19.0 43687 1.3454038
2005-06-08 09:46:43.997 162983.0 25.0 121508 1.3413246
2005-08-08 09:37:29.000 197467.0 20.0 143462 1.3764411
2005-08-24 11:21:37.997 57393.0 5.0 42770 1.3418672
2005-09-11 13:54:05.997 53729.0 5.0 39527 1.3592987
2005-11-08 00:00:00.000 193537.0 69.0 136906 1.4136385
2005-11-22 00:00:00.000 55031.0 33.0 38197 1.4407152
2005-12-05 00:00:00.000 40371.0 77.0 28082 1.4376112
2005-12-22 12:40:59.997 75170.0 3.0 52523 1.4311825
2006-03-02 00:00:00.000 239709.0 42.0 170405 1.4066935
2006-03-04 06:26:36.997 9639.0 23.0 6470 1.489799
2006-03-12 10:02:43.000 21993.0 1.0 16086 1.3672137
2006-03-15 00:00:00.000 8774.0 40.0 6687 1.3119019
2006-04-03 00:00:00.000 69570.0 31.0 46495 1.4962578
2006-04-04 00:00:00.000 8743.0 28.0 4606 1.8977643
2006-04-04 13:53:00.997 12284.0 6.0 3401 3.6108172
2006-04-05 00:00:00.000 10794.0 29.0 3438 3.139616
2006-04-06 00:00:00.000 9413.0 45.0 5001 1.8818473
2006-04-10 00:00:00.000 11058.0 30.0 7865 1.4059758
2006-04-14 00:00:00.000 23183.0 38.0 16281 1.4238423
2006-04-18 00:00:00.000 9898.0 37.0 7258 1.3635488
2006-04-21 03:19:31.000 16561.0 26.0 11848 1.3976707
2006-04-25 14:48:00.000 12287.0 3.0 8553 1.436572
2006-04-27 13:37:49.000 9793.0 96.0 7203 1.3593837
2006-05-02 00:00:00.000 11426.0 30.0 8135 1.4043757
2006-05-04 05:28:36.000 12277.0 22.0 8806 1.3940048
2006-06-08 00:00:00.000 123695.0 33.0 89478 1.3824068
2006-06-16 00:00:00.000 35327.0 37.0 24539 1.4396267
2006-06-29 00:00:00.000 48433.0 40.0 35226 1.3748829
2006-07-14 00:00:00.000 62915.0 57.0 44859 1.4024744
2006-08-10 00:00:00.000 106281.0 36.0 75810 1.401939
2006-08-17 00:00:00.000 25345.0 81.0 18123 1.398422
2006-08-28 00:00:00.000 40947.0 38.0 27573 1.4850397
2006-09-11 09:00:00.000 52187.0 15913.0 36698 1.4220666
2006-09-25 00:00:00.000 52902.0 30.0 37210 1.4216764
2006-10-06 00:00:00.000 54534.0 31.0 38244 1.4259119
2006-10-11 13:29:40.997 16380.0 5.0 12503 1.3100855
2006-11-29 00:00:00.000 197522.0 27.0 138746 1.423623
2006-12-01 00:00:00.000 10584.0 24.0 7602 1.3920821
2007-01-02 00:00:00.000 141284.0 34.0 101246 1.3954526
2007-01-12 02:57:03.997 60416.0 23.0 41700 1.4488249
2007-02-13 00:00:00.000 156270.0 75.0 109875 1.4222525
2007-02-16 00:00:00.000 17770.0 38.0 12325 1.441668
2007-03-05 12:23:00.000 73763.0 3.0 51503 1.43218
2007-03-08 04:11:49.997 16407.0 22.0 11428 1.4355587
2007-03-26 09:10:43.000 76336.0 20.0 53687 1.4218712
2007-04-05 12:31:28.000 64126.0 24.0 40172 1.5962859
2007-04-07 01:11:22.000 9244.0 28.0 6657 1.388405
2007-04-10 00:00:00.000 8924.0 38.0 6140 1.4531835
2007-04-24 21:01:00.000 73487.0 6.0 51689 1.421687
2007-04-26 09:01:48.997 9584.0 25.0 6650 1.441203
2007-04-28 04:09:21.000 9801.0 27.0 7037 1.3925831
2007-05-01 12:55:00.000 8781.0 26.0 6012 1.460336
2007-05-03 00:00:00.000 10570.0 53.0 7298 1.4481436
2007-05-04 21:49:27.000 12287.0 1.0 8680 1.415553
2007-05-08 06:06:45.997 8202.0 27.0 5511 1.4880261
2007-05-10 00:00:00.000 10920.0 49.0 7973 1.3696225
2007-05-12 00:44:10.000 11375.0 27.0 8223 1.3833151
2007-05-15 10:51:50.000 9453.0 27.0 6516 1.4507366
2007-05-18 08:44:36.997 17930.0 27.0 12651 1.4172792
2007-05-22 00:00:00.000 10089.0 74.0 7260 1.3894781
2007-05-23 21:07:38.000 12286.0 3.0 8604 1.4279405
2007-05-26 03:46:02.000 12287.0 6.0 8545 1.4377487
2007-05-30 21:24:29.997 12287.0 1.0 8663 1.4183308
2007-06-01 18:37:16.000 12287.0 1.0 8401 1.4623899
2007-06-05 00:00:00.000 9255.0 52.0 6491 1.4256008
2007-06-08 22:18:40.000 24574.0 3.0 17047 1.4415439
2007-06-12 09:42:14.997 9550.0 31.0 6410 1.4896272
9200-12-08 09:49:59.997 64286.0 1.0 45408 1.4157417
(150 row(s) affected)
What can I do to get SQL to use this index?
View 4 Replies
View Related
Aug 20, 2007
Hello All,
I have a series of Stored Procedure that has a query taking a join of 5 tables. These tables are quiet large with couple of them having around 10 million rows. As this is a DSS application having periodic data loads, I thought of creating Indexed View on top of these tables. Now the problem is that the Indexed View is not directly used by the optimizer. I need to change my queries and put a WITH (NOEXPAND) query hint to make sure the indexed views are used. This is inspite getting dramatic improvement in the query timings (from 64 secs down to 3 secs) after using the Indexed Views. I would like to know what can be the possible reason for the optimizer not using the Indexed View by itself. Is it because my Indexed View caters to multiple queries or I am missing out on something basic.
Thanks in Advance,
Mitesh Shah
View 4 Replies
View Related
Jul 6, 2001
I have SQL 7.0 SP2 on NT 4.0 SP5. My database is 180GIG. 23 Tables. It has been up and running for 2 years without any problems. All of a sudden my queries have started taking a long time to run. The optimizer has decided that table scans are better than indexes. If I use query hints they work just fine, but I can't modify all of our code to make these changes.
This is happening on all tables. Records counts are the in the same range they have always been.
Statistics and indexes are all fine and current. Have dropped and rebuilt both.
Has anybody else seen this behavior.
View 1 Replies
View Related
Jul 28, 2015
Running SQL 2012 SP2
I've got this query that runs in 30 seconds and returns about 24000. The table variable returns about 145 rows (no performance issue here), and the TransactionTbl table has 14.2 Million rows, a compound, clustered primary key, and 6 non-clustered indexes, none of which meet the needs of the query.
declare @CltID varchar(15) = '12345'
declare @TranDate datetime = '2015-07-25'
declare @Ballance table
(Ledger_Code varchar(4),
AssetID varchar(32),
CurrencyID varchar(3) )
[Code] ....
Actual execution plan shows SQL is doing an index seek, then a nested loop join, and then fetching the remaining data from the TransactionTbl using a Key Lookup.
I designed a new indexes based on the query, which when I force it's usage via an index hint, reduces the run time to sub-second, but without the index hint the SQL optimiser won't use the new index, which looks like this:
CREATE INDEX IX_Test on GLSchemB.TransactionTbl (CltID, Date) include (Ledger_Code, Amount, CurrencyID, AssetID)and I tried this:
CREATE INDEX IX_Test on GLSchemB.TransactionTbl (CltID, Date, Ledger_Code, CurrencyID, AssetID) include (Amount)and even a full covering index!
I did some testing, including disabling all indexes but the PK, and the optimiser tells me I've got a missing index and recommends I create one EXACTLY like the one I designed, but when I put my one back it doesn't use it.
I though this may be due to fragmentation and/or stats being out of date, so I rebuilt the PK and my index, and the optimiser started using my index, doing an index seek and running sub-second. Thinking I had solved the problem I rebuilt all the indexes, testing after each one, and my index was used BUT as soon as I flushed the related query plan, the optimiser went back to using a less optimal index, with a seek and key lookup plan and taking 30 seconds.
For now I've resorted to using the OPTION (TABLE HINT(G, INDEX(IX_Test))) to force this, but it's a work around only. Why the optimiser would select a less optimal query plan?
View 8 Replies
View Related
Mar 14, 2008
I am having an issue with large queries using Microsoft SQL Server 2005 - 9.00.2221.00 (X64).
I have a query with many INNER/LEFT OUTER/RIGHT OUTER joins which is taking very very very long to run. This looks exactly like this problem described in http://support.microsoft.com/kb/318530. However, this doc says it was fixed in SP1, which is already installed.
Basically I have a query:
SELECT ....
FROM TABLEA
INNER JOIN TABLEB ...
LEFT OUTER TABLEC...
LEFT OUTER TABLED...
RIGHT OUTER TABLEF...
LEFT OUTER TABLEJ..
LEFT OUTER TABLEH...
LEFT OUTER TABLEI...
RIGHT OUTER TABLEK...
LEFT OUTER TABLEM..
... 17 joined tables in all......
WHERE TABLEB.field1 = 'abc'
The query plan for this is using TABLEA as the "main" table and joining everything else to it. The problem is, TABLEA has 117 MILLION records. TABLEB has 10,000 records which match the WHERE. I stopped this query after it ran for 62 HOURS.
If I simply change the query to:
SELECT ....
FROM TABLEB
INNER JOIN TABLEA ...
LEFT OUTER TABLEC...
LEFT OUTER TABLED...
RIGHT OUTER TABLEF...
LEFT OUTER TABLEJ..
LEFT OUTER TABLEH...
LEFT OUTER TABLEI...
RIGHT OUTER TABLEK...
LEFT OUTER TABLEM..
... 17 joined tables in all......
WHERE TABLEB.field1 = 'abc'
The query runs in 15 mins. The query plan now uses TABLEB and the WHERE clause to join all the other tables.
The problem is, this query is generated from a report writter, and I have no control over the way it creates the SQL code.
Any ideas how I can fix this?
View 1 Replies
View Related
Mar 12, 2008
What does mean by Optimizer in MS SQL ? What is it ?
How do you use it ? what are the benifits of use it ?
View 4 Replies
View Related
May 18, 2015
Here is my SQL SEVER QUERY.
Â
Need to change vertical execution of query into horizontal in sql server
Â
SELECT TL.TERMINAL_ID,T.IP_ADDRESS, T.LOGICAL_CONNECT_STATUS, SI.SCHEDULER_ID,
SI.INSTRUCTION, SI.GROUP_ID, SI.MAX_READ_RETRIES, SI.DATA_CHAR, SI.SCHEDULE_TYPE,SI.FILEPATH_FLAG,
T.STATION_NAME,T.BANK_ID FROM SCHEDULERINFO SI WITH (NOLOCK)
INNER JOIN TERMINALGROUP TG WITH (NOLOCK) ON SI.GROUP_ID = TG.GROUP_ID INNER JOIN TERMINALGROUPLINK TL WITH (NOLOCK) ON TG.GROUP_ID = TL.GROUP_ID
[Code] ....
Output of the query is:
SCHEDULER_ID
TERMINAL_ID
IP_ADDRESS
LOGICAL_CONNECT_STATUS
INSTRUCTION
[Code] ....
Â
Desire output required:
Â
SCHEDULER_ID
TERMINAL_ID
IP_ADDRESS
LOGICAL_CONNECT_STATUS
INSTRUCTION
[Code] ....
View 4 Replies
View Related
Mar 25, 2006
Hi,
im developing an application for my university final year project and i want to lock the table before the query executes to ensure that no other changes are allowed (sorry if im confusing)
So my question , really does anyone know how to do this and is it possible ?, for example in coldfusion a query is surrounded with cflock or something similiar.
Appreciate any and all help guys
Many thanks in advance
andy
View 1 Replies
View Related
Nov 17, 2006
Hello Friends,
I am totally new to locking field so I need your help.
Below are my environment details:
OS = Windows 2003 + Service Pack 1
SQL SERVER= SQL Server Enterprise Edition + Service Pack 4
My Organization is using an application that is designed in PowerBuilder. The problem is so many times this application got hang due to blocking in sql server.
I have diagnosis the problem in the below steps,
Waiting spid = 192
Select * from sysprocesses
Where spid = 192
Dbcc inputbuffer (192)
Then
Select blocked,* from sysprocesses
Where spid = 192
Returns
201
I had repeated the steps till I got BLOCKED = 0, STATUS = sleeping and CMD = AWAITINGCOMMAND. By this way I found the blocking spid.
And DBCC INPUTBUFFER (blocked spid) is a SELECT statement.
In sp_lock spid it is showing that spid is holding a shared lock, where my application had fetched all the data and also transaction had been commited.
I cannot find the solution please advice me what to do. Why select statement is holding a lock. End users are really frustrated.
Thanks a billion in advance.
View 7 Replies
View Related
Apr 2, 2015
I am trying to print Companies with less than 100 employees for all dates.Here's my table structure
Create table CompanyEmployeeArchive(
Company varchar(100) not null, Employees int, Dateinserted date)
Insert into CompanyEmployeeArchive values('Microsoft',1001,'2015-01-01')
Insert into CompanyEmployeeArchive values('Microsoft',1050,'2015-02-01')
Insert into CompanyEmployeeArchive values('Microsoft',1600,'2015-03-01')
Insert into CompanyEmployeeArchive values('IBM',10,'2015-01-01')
Insert into CompanyEmployeeArchive values('IBM',80,'2015-02-01')
Insert into CompanyEmployeeArchive values('Apple',90,'2015-01-01')
Insert into CompanyEmployeeArchive values('Apple',900,'2015-02-01')
Insert into CompanyEmployeeArchive values('Apple',1000,'2015-03-01')
I want companies that have employees less than 100 for all dates i.e. Only IBM. Apple has < 100 employees only on one month.Select Company, dateinserted, employees from CompanyEmployeeArchive group by company,dateinserted,employees having employees < 100 order by company, dateinserted this query lists Apple too. How can I change the query so Apple does not show up in the list.
View 4 Replies
View Related
Aug 6, 2015
I have below script
CREATE TABLE dbo.TestPivot(
CollectionDate DATETIME,
Products VARCHAR(40),
ItemCount INT
)
INSERT INTO dbo.TestPivot
SELECT '4/1/2015','Benz' , 20
[Code] ....
-- Original Output
ProductsApr 2015May 2015Jun 2015
Benz10-800NULL
Toyota5NULL-180
****Required output where ever we have negative values we need to display message Invalid out put message for those negative rows
ProductsApr 2015May 2015 Jun 2015
Benz10Invalid NULL
Toyota5NULL Invalid
View 2 Replies
View Related
Oct 15, 2015
Hotkey combination to be able to change the server connection from Server A to Server B in a query window in SQL Management Studio? This is so you do not have to Right click, chose "Connection" and then chose "Change Connection".
View 2 Replies
View Related
Apr 26, 2014
I have two records...is having pid=10
idpid
10110
10210
1035
1046
1065
then i executed the below query..
update Child set pid=10 where id in (101,102)
the sql server showing message like
(2 row(s) affected) but as per data no records updated so i need to change this message type
if i ran the above update query the the result should be like
(0 row(s) affected)
is there any way to change this...
View 3 Replies
View Related
May 7, 2008
Hello, first of all thanks to share greats posts.
I try do some tests and I get one doubt, why the optimizer don€™t make a constant scan in normal tables, for instance:
Code Snippet
--drop table #tmp
create table #tmp (id Int Identity(1,1) Primary key, name VarChar(250))
go
insert into #tmp(name) values(NEWID())
insert into #tmp(name) values(NEWID())
go
set statistics profile on
go
-- Execution plan create a Constant Scan
select * from #tmp
where id = 1 and id = 5
go
set statistics profile off
GO
--drop table tmp
create table tmp (id Int Identity(1,1) Primary key, name VarChar(250))
go
insert into tmp(name) values(NEWID())
insert into tmp(name) values(NEWID())
go
set statistics profile on
-- Why execution plan does not create a Constant Scan for this case?
select * from tmp
where id = 1 and id = 5
go
set statistics profile off
Thanks
View 4 Replies
View Related
Jul 20, 2005
I tried this:use northwindgoSELECT OrderDateFROM Orders WHERE OrderDate > '19950101'see the query plan? okSELECT OrderDate, EmployeeIdFROM Orders WHERE OrderDate > '19950101'see the query plan? what appened?the only way to make an index seek instead of an index scan is toforce theindex usage ( with(index=orderdate) ), but I don't like this solutionalso try this:SELECT *FROM Orders WHERE employeeId > 9andSELECT *FROM Orders WHERE employeeId > 8Can someone explain why this appens? and how can I overturn theperformance loss problem (well not in orders table, but in my tablethere are 300K records and making a scan to retrieve 50 records is notexactly what I want)thanks to all
View 2 Replies
View Related
Oct 31, 2007
Hi,
The product uses a simple stored procedure. The query times out when there are five million records in the table. It looks like below:
DECLARE
@CreatedStatus int,
@PendingStatus int;
SELECT
@CreatedStatus = [LV].[ufGetCode]('SaleStatus', 'Created'), -- which returns 1001
@PendingStatus = [LV].[ufGetCode]('SaleStatus', 'Pending'); -- which returns 1002
SELECT *
FROM LV.Detail WITH (READPAST)
WHERE Status = @CreatedStatus OR
Status = @PendingStatus;
Status columne is non-clustered index. The query takes more than 30 seconds. But when i use the query below:
SELECT * FROM LV.Detail WITH (READPAST) WHERE Status = 1001 or Status = 1002
The query is done very fast. No time out.
Anybody knows what makes difference? I am using SQL 2005.
View 3 Replies
View Related
Aug 5, 1999
While investigating performance problems within an application recently I carried out some tests using SET SHOWPLAN ON.
I had a query like this within a stored procedure:
SELECT MAX(X) FROM Y WHERE Z LIKE @MYVAR
Where @MYVAR was passed in. I discovered that SQL Server did a Table Scan even when Z had an index on it. A problem with 200,000 rows!
If I said
SELECT MAX(X) FROM Y WHERE Z LIKE 'HELLO%'
(i.e., used a constant instead of a variable) SQL Server did use the index correctly and did not do a table scan.
I got around this by rewriting my statement:
SELECT MAX(X) FROM Y (INDEX=MYINDEX) WHERE Z LIKE @MYVAR
in other words by manually specifying the index I had created on the Z column.
Hope this helps someone.
View 2 Replies
View Related
Feb 1, 2007
Way back when, and at least in version 7 IIRC, the query optimizer gaveup when the where clause in a statement contained more than 4 searchconditions.Does anyone know if such a limitation still exist in MS SQL 2005? TheBOL seems to be silent on the issue.Boa
View 2 Replies
View Related
Jul 20, 2005
I'm very puzzled by the choice of NC index being made by the optimizerin this example. I don't actually think it should use an NC index atall.I have:Table: CustomerStatus_TSingle data page19 recordsClustered Index on CustomerStatusID:CREATE TABLE [CustomerStatus_T] ([CustomerStatusID] [int] NOT NULL ,[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Description] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[Code] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CodeAlt] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Ordinal] [int] NULL ,[Default] [int] NULL ,[Display] [bit] NOT NULL ,[StatusType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[DateCreated] [smalldatetime] NULL ,[DateUpdated] [smalldatetime] NULL ,[DateArchived] [smalldatetime] NULL ,CONSTRAINT [PK_ROMS_CustomerStatus] PRIMARY KEY CLUSTERED([CustomerStatusID]) ON [PRIMARY]) ON [PRIMARY]If I run the following query, it does exactly what I expect and scansthe clustered index:SELECT customerStatusID, [Name] FROM CustomerStatus_TWHERE dateArchived IS NULLAND Display = 1AND StatusType = 'Q‘and gives the following QEP and IO statistics:|--Clustered Index Scan(OBJECT:([Reach_Roms].[dbo].[CustomerStatus_T].[PK_ROMS_CustomerStatus]),WHERE:(([CustomerStatus_T].[DateArchived]=NULL AND[CustomerStatus_T].[StatusType]='Q') ANDConvert([CustomerStatus_T].[Display])=1))Table 'CustomerStatus_T'. Scan count 1, logical reads 2, physicalreads 0,read-ahead reads 0.If I now put a NC index on the statustype column:create index ix_nci_statustype on customerstatus_t(statustype)the query plan changes to:SELECT customerStatusID, [Name] FROM CustomerStatus_TWHERE dateArchived IS NULLAND Display = 1AND StatusType = 'Q‘|--Filter(WHERE:([CustomerStatus_T].[DateArchived]=NULL ANDConvert([CustomerStatus_T].[Display])=1))|--Bookmark Lookup(BOOKMARK:([Bmk1000]),OBJECT:([Reach_Roms].[dbo].[CustomerStatus_T]))|--IndexSeek(OBJECT:([Reach_Roms].[dbo].[CustomerStatus_T].[ix_nci_statustype]),S EEK:([CustomerStatus_T].[StatusType]='Q') ORDEREDFORWARD)Table 'CustomerStatus_T'. Scan count 1, logical reads 7,physical reads 0, read-ahead reads 0.For some bizarre reason, the optimizer thinks that a NC index lookupon a single-page table, which ultimately costs 7 IOs, is cheaper thana table (or Clustered Index) scan of a single page. Why? Theshowplan cost also shows that it expects the NC index to be cheaper(which is presumably why it goes and uses it), but even after runningUPDATE STATISTICS on the table it still chooses the same idiotic queryplan.Any thoughts, or has anyone seen similar behaviour before, and cananyone please explain it to me?p.s. I don't actually WANT to put a NC index on this table, but Inoticed the behaviour by accident which is why I'm asking the question:-)
View 3 Replies
View Related
Apr 3, 2001
Hi all,
This table,
create table test (
t1 int,
t2 char(4) )
has about a few hundred rows. I have created a nonclustered index on column t1.
When I execute the following query, the query execution plan shows that the optimizer performs an index seek using the index on col. t1.
select * from test where t1 = 4
But, when I run the following query, using isnull(), the optimizer always performs a table scan.
select * from test where isnull(t1, 0) = 4
Could someone please explain why?
Thanks in advance,
Praveena
View 1 Replies
View Related
Aug 25, 2015
Is there a DMV or similar in SQL 2012, or SQL 2008, that shows when a statistic was last used by the optimizer? I would like to cleanup some of the auto-generated stats, assuming it's possible to do so. In particular I'm looking to drop those statistics that were created by one-off queries, data loads, etc, and are now doing nothing but adding to the execution time of Update Statistics jobs.
View 6 Replies
View Related
Feb 8, 2007
declare @ContactId as integerset @ContactId = 5select *from Person.Contactwhere ContactId = @ContactIdOR @ContactId = -1If you run this in SQL 2005 on the AdventureWorks database,why the logical reads is 561Table 'Contact'. Scan count 1, logical reads 56and not 2 when you run without the second OR condition:declare @ContactId as integerset @ContactId = 5select *from Person.Contactwhere ContactId = @ContactIdHow can i use the same SP and either get one record returnedby passing the ID of the field, or pass a dummy parameter like-1 in order to get ALL the records returned.In this case even when i pass a parameter like ContactID = 5there is still a table scan (clustered index scan in this case)happening for the other OR condition.There's no method to tell SQL to start checking the first conditionwhether or not it is true then if it is false then check the second ORconditon. On the same topic does this mean all OR conditions areALWAYS verified regardless if one of them has already been determinedto be True?Thank you
View 2 Replies
View Related
Jul 20, 2005
Guys,what I need is a tool which gives details on the choice of anexecution plan by the SQL Server. For example, the cost for a hashjoin might be 200 and 100 for a nested loop, and therefore a nestedloop is used. Same thing for the access paths for each table/viewinvolved. In Oracle, we turn on event 100053 to see this kind of info.ThanxDaniel
View 2 Replies
View Related
Oct 25, 2006
An interesting discussion yesterday. One of the programmers asked about the use of the NOLOCK optimizer hint with an iterator table aka table of numbers. His comment was that this optimizer hint was not efficient. Rather than give a knee-jerk response I thought it would be better to ask. The main circumstance is that the iterator table is completely static with a fill factor of 100%. My purpose is to eliminate lock contention if I can.
Are there reasons to not use the NOLOCK hint in this case to potentially improve performance?
Dave
View 6 Replies
View Related
Oct 11, 2007
Hi all
I cannot find this info anywhere. We have Hyperion 8.x running against SQL 2000. We will upgrade side-by-side in the coming weeks. It appears that Hyperion 8.x is assured to continue to work without issue with a 2K5 database in 80 compatibility level.
I have seen the BOL table on differences between 80 and 90 (specifically affects us - WITH on Index hints), but what I wonder are about 3 specific features.
1. Do you still get DMV capabilities in 80 mode? (I ran a test on sys.dm_db_index_usage_stats for an 80 compat DB, creating index and using query to hit index, and yes - it does look as if this DMV does get populated).
2. Can you still Table Partition in 80 mode? (Ran a test in DEV, and yes - this does look possible - any gotchas?)
3. Does the optimizer behave quite differently in 80 mode?
I have tested in DEV moving a DB from 80 to 90 and back, and this seems to work with no problems.
TIA from Grand Fenwick!! ;-)
View 1 Replies
View Related
Jun 28, 2001
I have a table X with 61390 rows on it. IT is a crucial table which is being read and updated constantly. If I create a clustered index on the Primary Key (identity column) and run UPDATE x SET c1 = 1234 where PKCOL = 4321 the best lock I can get is a table Lock. If I create a compound non-clustered index on C1 and PKCOL the best lock I can get is a Page Lock. PAge Lock is OK but as this table is in High demand I want a row lock so others can carry on referenceing other parts of the table. WHy does it take a page lock and not a Row lock?
View 1 Replies
View Related
Jul 7, 1999
Hi,
We are becoming increasingly more involved with SQL Server 7.0 development.
Can someone show me a good example of ROW LOCKING with exclusive and share
modes?
If I am starting a transaction with a SELECT and later an UPDATE, what is
the best way to handle locking?
Thank you for any help on this issue.
Steve
View 1 Replies
View Related
