SQLServerCentral.com / SQL Server 2005 Performance Tuning / SQL Server 2005 InstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 20:06:55 GMT20Float vs Decimal?http://www.sqlservercentral.com/Forums/Topic522397-360-1.aspxDoes anyone have any opinions concerning the performance of Float vs decimal data types?For example I have a table that stores balances, turnovers, and currency rates all of which were originally set up a float data types. The types of operations I am performing on the data are arithmetic... (no comparisons that would require converting float values to numeric etc) mainly calculating currency balances... Because the table is small, (only about 100k records long & I would expect it to grow at a rate of about 10% annually) I'm guessing that converting to Decimal data types might not improve performance that much, if at all.Does anyone have any thoughts about how significant an improvement I'd get?Tue, 24 Jun 2008 03:26:31 GMTBen LeightonFind last business day from table with date and day in SQL 2008 and 2012http://www.sqlservercentral.com/Forums/Topic1838607-360-1.aspxHi All,I need last business day from a table with ID,date and day column in it.For e.g. ID Date Day1 1-Jan-2016 Friday2 2-Jan-2016 Saturday3 3-Jan-2016 Sunday..29 29-Jan-2016 Friday30 30-Jan-2016 Saturday31 31-Jan-2016 Sunday32 1-Feb-2016 Monday33 2-Feb-2016 Tuesday..60 29-Feb-2016 Mondayand so on....so I need 29-Jan-2016 Friday(Excluding Saturday and Sunday)29-Feb-2016 Monday...ThanksTue, 29 Nov 2016 04:15:41 GMTprafullaahirraowhen one column is updated, want to update another column in same table using trigger.http://www.sqlservercentral.com/Forums/Topic575707-360-1.aspxDear All,I have a table in which there are fields like Quantity, Price, Total etc.There's is also a primary key field in this table.We have an interface where user can change the quantity (only quantity). I want to write a trigger on this table which calculates Total (qty * price) whenever qantity is updated.How can I do it..?. How will I know for which row the quantity is updated..?Pleas help...Thanks in advance..Santhosh Nair.Wed, 24 Sep 2008 23:18:08 GMTJoy Smith SanTable partitioning and MAX queries performance problemhttp://www.sqlservercentral.com/Forums/Topic890635-360-1.aspxHi All,Just implemented partitioning in SQL Server 2005. Which should be a great tool for better flexibility and performance. However I'm pretty disappointed running into performance problems while running simple queries like these:select max(id) from tbl_testIf the table contains 50 miljons of rows. An index scan of 50 miljon rows occurs despite the existence of the index. When using an unpartitioned table an index seek is used as one would expect.Then I found out this bug is also confirmed by Microsoft:http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/ac2f13d7-875a-4f0c-b119-c654e4990ea7/I'm pretty disappointed in Microsoft especially the fact they are not able to solve this problem.I was wondering if some of you also ran into this problem and what workarounds you have used.Best Regards, Peter (SQL Server DBA)Fri, 26 Mar 2010 07:56:17 GMTpeterjonkCan't get sp_create_plan_guide to workhttp://www.sqlservercentral.com/Forums/Topic1837059-360-1.aspxI have the following SQL generated by a third party application that is using a wrong index and create undue parallelisation.I tried to create a plan guide without success.Here is a the query text : (@P0 int,@P1 int,@P2 nvarchar(4000),@P3 int,@P4 int,@P5 int,@P6 decimal(38,0),@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 int,@P11 nvarchar(4000),@P12 nvarchar(4000))SELECT TOP 1 * FROM MVXJDTA.MITALO (NOLOCK) WHERE MQCONO= @P0 AND MQTTYP= @P1 AND MQRIDN= @P2 AND MQRIDO= @P3 AND MQRIDL= @P4 AND MQRIDX= @P5 AND MQRIDI= @P6 AND (MQWHSL= @P7 AND MQBANO= @P8 AND MQCAMU= @P9 AND MQPLSX= @P10 AND MQWHLO= @P11 AND MQITNO&lt; @P12 ) ORDER BY MQCONO DESC ,MQTTYP DESC ,MQRIDN DESC ,MQRIDO DESC ,MQRIDL DESC ,MQRIDX DESC ,MQRIDI DESC ,MQWHSL DESC ,MQBANO DESC ,MQCAMU DESC ,MQPLSX DESC ,MQWHLO DESC ,MQITNO DESC ,MQSOFT DESC [u]It does not accept :[/u] (error found in @stmt )sp_create_plan_guide @name = N'MITALO Jct', @stmt = N'(@P0 int,@P1 int,@P2 nvarchar(4000),@P3 int,@P4 int,@P5 int,@P6 decimal(38,0),@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 int,@P11 nvarchar(4000),@P12 nvarchar(4000))SELECT TOP 1 * FROM MVXJDTA.MITALO (NOLOCK) WHERE MQCONO= @P0 AND MQTTYP= @P1 AND MQRIDN= @P2 AND MQRIDO= @P3 AND MQRIDL= @P4 AND MQRIDX= @P5 AND MQRIDI= @P6 AND (MQWHSL= @P7 AND MQBANO= @P8 AND MQCAMU= @P9 AND MQPLSX= @P10 AND MQWHLO= @P11 AND MQITNO&lt; @P12 ) ORDER BY MQCONO DESC ,MQTTYP DESC ,MQRIDN DESC ,MQRIDO DESC ,MQRIDL DESC ,MQRIDX DESC ,MQRIDI DESC ,MQWHSL DESC ,MQBANO DESC ,MQCAMU DESC ,MQPLSX DESC ,MQWHLO DESC ,MQITNO DESC ,MQSOFT DESC ', @type = N'SQL', @module_or_batch = NULL , @params = N'@P0 int,@P1 int,@P2 nvarchar(4000),@P3 int,@P4 int,@P5 int,@P6 decimal(38,0),@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 int,@P11 nvarchar(4000),@P12 nvarchar(4000)',@hints = N'OPTION (TABLE HINT ( MITALO, INDEX( MITALO10) , NOLOCK, FORCESEEK ))';[u]It does not work with : [/u]sp_create_plan_guide @name = N'MITALO Jct', @stmt = N'SELECT TOP 1 * FROM MVXJDTA.MITALO (NOLOCK) WHERE MQCONO= @P0 AND MQTTYP= @P1 AND MQRIDN= @P2 AND MQRIDO= @P3 AND MQRIDL= @P4 AND MQRIDX= @P5 AND MQRIDI= @P6 AND (MQWHSL= @P7 AND MQBANO= @P8 AND MQCAMU= @P9 AND MQPLSX= @P10 AND MQWHLO= @P11 AND MQITNO&lt; @P12 ) ORDER BY MQCONO DESC ,MQTTYP DESC ,MQRIDN DESC ,MQRIDO DESC ,MQRIDL DESC ,MQRIDX DESC ,MQRIDI DESC ,MQWHSL DESC ,MQBANO DESC ,MQCAMU DESC ,MQPLSX DESC ,MQWHLO DESC ,MQITNO DESC ,MQSOFT DESC ', @type = N'SQL', @module_or_batch = NULL , @params = N'@P0 int,@P1 int,@P2 nvarchar(4000),@P3 int,@P4 int,@P5 int,@P6 decimal(38,0),@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 int,@P11 nvarchar(4000),@P12 nvarchar(4000)',@hints = N'OPTION (TABLE HINT ( MITALO, INDEX( MITALO10) , NOLOCK, FORCESEEK ))';Thanks for any hint for this to workSorry wrong forum, should be in SQL Server 2012, reposted ..Tue, 22 Nov 2016 07:14:16 GMTjean_chr_thielPuzzling intermittent performance issuehttp://www.sqlservercentral.com/Forums/Topic1814234-360-1.aspxSo we have a situation where an application does an item lookup. On average that item lookup calls 3 or 4 stored procedures. On average each of those stored procedures runs for about 5-10ms. However, intermittently and for no apparent reason, sometimes they run for a much longer period. Generally when this happens all 4 run for a longer period. And by longer I mean anywhere between 1 and 2 seconds. Therefore the total time for an item lookup that is usually a few milliseconds suddenly becomes 6 to 8 seconds. Things we have looked at so far:- I am not a DBA but my trusted DBA's tell me that all indexes are in place, all have been rebuilt, stats updated, etc. - CPU/Memory contention - there does not seem to be any memory contention on the servers. These servers have 4GB RAM. SQL max memory is set to 3GB but appears to only be using about 1.5GB based on some analysis we have done by tweaking this number and monitoring the available memory in the OS. Even with the max memory set to 3GB the OS shows around 800MB of RAM available so it seems there is plenty for SQL to claim if it required it. While observing a trace and perfmon together no significant CPU or memory spikes are observed during periods where we see this behavior.- Disk contention - Average disk queue lengths are not getting above 2 with any regularity and disk sec/read and disk sec/write are both below 0.003. in addition running a perfmon with a profiler trace we don't observe any disk metrics spiking at the time of this behavior. This behavior occurs across over 1000 of the same servers with the same kind of workload. (These are branch servers). If on a given day there are 1000 lookups we might observe somewhere between 20 and 50 of them displaying this behavior. Many of these servers are a good few years old but without being able to identify the root cause I don't want to blame this on the underlying hardware. I don't believe this is related to any recent changes. The problem has likely been around for a long time but has only recently become a priority in terms of getting it resolved. Any thoughts and ideas on where to look next would be appreciated. I am not a DBA but I have both DBA's and Sysadmins available to look at any aspect of this.Thu, 01 Sep 2016 00:54:35 GMTkreilly 50203Want solution on access utilization of RAM by SQL server 2005http://www.sqlservercentral.com/Forums/Topic1811145-360-1.aspxDear All,My Database is SQL Server 2005. It is having 32Gb ram. In which Sql server taking 28 gb. After investigating why it is taking 28gb ram I came to know BufferPool taking lot of space for dirty page and clean page. Due to access of ram utilization in performance point of view I am facing problem.:doze:Please can I get any solution on it which solve my problem.:-)Sun, 21 Aug 2016 22:50:33 GMT2MExecution Plan -- Clustered Index Updatehttp://www.sqlservercentral.com/Forums/Topic660590-360-1.aspxWhen looking at the execution plan of a query it shows a [b]'Clustered Index Update' [/b]@ a cost of 85%. I understand what it is ... but does anyone know what can cause that number to jump so high and how to get it down to some manageable level ??Any and all help is welcome and greatly appreciated.Thu, 19 Feb 2009 10:43:21 GMTSQLDraggonLogical Readshttp://www.sqlservercentral.com/Forums/Topic486922-360-1.aspxwhen i execute my main sp , inner sp showing read count as 6000 ,but when execute that perticular sp its not showing that much reads . Explain why its showing like .......Fri, 18 Apr 2008 00:26:36 GMTsudhakarawhich is better to user the cross join or while loop ?http://www.sqlservercentral.com/Forums/Topic1372049-360-1.aspxHi Please tell me which is better for the performance impact ?1. Insert into #temp table Using the cross join with tables.2. Insert into #temp table using the while loop to repeat the same data no of times.Fri, 12 Oct 2012 05:54:18 GMTnitin.varshneyPerformance Issueshttp://www.sqlservercentral.com/Forums/Topic1800773-360-1.aspxDear allIn my production server frequently(every four to six hour) i am getting time out problem in my application .Whenever i am facing this problem just i execute sp_updatestats, then its working fine for next four more hours. Daily night executed index rebuild also.Please suggest me permanent solution.Sun, 10 Jul 2016 23:16:20 GMTvs.satheeshQuery performance change depending where condition date valuehttp://www.sqlservercentral.com/Forums/Topic1800167-360-1.aspxHi allI have and issue with a query in SQL Server 2012SELECT top 1000 firmas.TipoPers, firmas.CodPers, GRUPO1.Limite, GRUPO1.DisponibleFROM ( select TipoPers, CodPers, Num_Grupo from TABLA1 WHERE FECDATOMES = CAST (convert(varchar(8),'20160531') as datetime) ) firmas LEFT JOIN ( SELECT grupo, SUM(limite) as Limite, SUM(disp) as Disponible FROM TABLA2 WHERE FECDATOMES = CAST (convert(varchar(8),'20160531') as datetime) group by grupo ) GRUPO1ON firmas.Num_Grupo = GRUPO1.grupoIt is a production enviroment and I´m allowed to see only this stats:SQL Server parse and compile time: CPU time = 31 ms, elapsed time = 40 ms.(1000 filas afectadas)Table 'TABLA2'. Scan count 9, logical reads 2151, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'TABLA1'. Scan count 9, logical reads 186, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 249 ms, elapsed time = 31 ms.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.It works fine, but when i run the query with a date value of 20160630 the stats change to worse:SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 6 ms.(1000 filas afectadas)Table 'TABLA2'. Scan count 1000, logical reads [b]1916000[/b], physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'TABLA1'. Scan count 1, logical reads 32, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: [b]CPU time = 321141 ms, elapsed time = 412712 ms.[/b]SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.I must use a TOP 1000 because in the second case the query never finished. Without the TOP the first case finished in 10 seconds max and return 400.000 rows.RegardsThu, 07 Jul 2016 06:21:53 GMTeliseoaznarteInterview questions on SQL Server 2005.http://www.sqlservercentral.com/Forums/Topic659105-360-1.aspxCan anyone send me some interview questions pls.I recently got into DBA and I wanna stick to it as am very much interested in this.The problem is that my manager is forcing me to work on other technologies as there is no much work on database in our company. I don't want to switch to any other technology now but I have never given any interview for DBA post.Kindly help me with questions and also lemme know if there's vacance for SQL Server 2005 DBA.Thanks in advance.Tue, 17 Feb 2009 23:52:57 GMTJoy Smith SanMicrosoft ACE OLEDB 12.0 X64 Driver freezing using open rowsethttp://www.sqlservercentral.com/Forums/Topic1208903-360-1.aspxHi I am running SQL Server 2005 x64 sp4 on Windows Server 2003sp2 x64 in production. (8gb sql server configuredmin 100mb max 6400mb)We have recently moved an application and database to this server and upgraded to using the ms ace oledb x64 driver for an automated process that uses openrowset to import excel files.It works for a while and then hangs. No error messages the process just keeps running and does not stop.The only way to resolve it is to restart the sql service.I have setup a test machine like for like and scheduled a job to run the command below every minute. After about 500 runs it freezes as well. Restart the sql service and it stars running fineDECLARE @cmd VARCHAR(MAX) SET @cmd='SELECT * FROM OPENROWSET( ''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;Extended Properties=Excel 12.0;Database=D:\testme.xls;HDR=NO;IMEX=1'', ''SELECT * FROM [Sheet1$]'')' EXEC (@cmd) goSun, 20 Nov 2011 03:00:16 GMTgrowlStock Volume calculationhttp://www.sqlservercentral.com/Forums/Topic1788691-360-1.aspxHi there, I have a raw table that I get every friday night after stocks close, which gives 45 days worth of stock volume for certain companies. I am trying to build a work table that one record per month stock volume. The raw table from last Friday[code="sql"]create table raw.stockPrice(companyid char(10) not null, Volume numeric(20,4), VolumeDate smalldatetime)INSERT INTO raw.StockPrice values('AB1234','12.2','2016-04-01')[/code].. every day there are such records until 30th April[code="sql"]INSERT INTO raw.StockPrice values('AB1234','15.2','2016-05-01')INSERT INTO raw.StockPrice values('AB1234','15.3','2016-05-13')[/code]My output table Should have CompanyID, Stock Volume added per month i.e. all April values added to create record 1, All May records to be added to create record 2. VolumeDate should be latest Friday.THis runs on Saturday night. At any point in time, there should be only one record per month.THe output should look like this if it was run on 14th May[code="sql"]INSERT INTO work.StockPriceVolume values('AB1234','32.4','2016-04-29') -- last friday of April and addition of all volume from AprilINSERT INTO work.StockPriceVolume values('AB1234','19.4','2016-05-13) [/code]THe output should look like this if it was run on 21st May[code="sql"]INSERT INTO work.StockPriceVolume values('AB1234','32.4','2016-04-29') [/code]-- last friday of April and addition of all volume from April[code="sql"]INSERT INTO work.StockPriceVolume values('AB1234','20.4','2016-05-20) [/code] --13th may knocked out of table but insert next friday date and add all volume until 20th MayTHe output should look like this if it was run on 28th May[code="sql"]INSERT INTO work.StockPriceVolume values('AB1234','32.4','2016-04-29')[/code] -- last friday of April and addition of all volume from April[code="sql"]INSERT INTO work.StockPriceVolume values('AB1234','28.4','2016-05-27) [/code] --20th may knocked out of table but insert next friday date and add all volume until 27th MayTHe output should look like this if it was run on 4th June[code="sql"]INSERT INTO work.StockPriceVolume values('AB1234','32.4','2016-04-29')[/code] -- last friday of April and addition of all volume from April[code="sql"]INSERT INTO work.StockPriceVolume values('AB1234','28.4','2016-05-27) [/code] --last friday of May and addition of all volume from May[code="sql"]INSERT INTO work.StockPriceVolume values('AB1234','28.4','2016-06-03)[/code] --insert next friday date and add all volume until June 3rdThis is the query I have,[code="sql"]INSERT INTO work.StockPriceVolume(PriceDate,CompanyID,Volume)SELECT maxmonthlydate as PriceDate,CompanyID,VolumeFROM(SELECT CompanyID,YEAR(Pricedate) as Yr,MONTH(pricedate) as Mnth,SUM(Volume) as volume,max(PriceDate) as maxmonthlydateFROM( SELECT DISTINCT MAX(asofdate) as PriceDate,BB.CompanyID,Volume FROM(SELECT DATEDIFF(DAY,-2,asofDate)/7 as SalesWeekly ,SUM(cast(value as NUMERIC(22,4))) AS Volume ,CompanyID FROM raw.stockPrice R GROUP BY DATEDIFF(DAY,-2,asofDate)/7,CompanyID)innerqryINNER JOIN raw.stockPrice BBon BB.Companyid=innerqry.Companyid and SalesWeekly=DATEDIFF(DAY,-2,asofDate)/7GROUP BY BB.CompanyiD,SalesWeekly,innerqry.Volume) tt GROUP BY YEAR(Pricedate),MONTH(pricedate),CompanyID)qrywhere maxmonthlydate &gt; (select MAX(PriceDate) from work.StockPriceVolume where CompanyID=qry.CompanyID)[/code]As you see, I have 2 rows for May 13th and 20th. How can I delete 13th and have only 20th.THanksRsSun, 22 May 2016 05:36:07 GMTrash3554Covering Index vs Included Columnshttp://www.sqlservercentral.com/Forums/Topic509318-360-1.aspxHi Guys!Can anyone tell me if there is any functional or performance difference between designing a covering index and an index that includes columns that will be queried.Let's say I wanted to sum up the total quantity ordered by product in a date range.SELECT ProductID, SUM(QtySold) AS MarchQtyFROM OrderDetailWHERE OrderDate &gt;= '3/1/08'AND OrderDate &lt; '4/1/08GROUP BY ProductIDWould there by any performance difference between:CREATE NONCLUSTERED INDEX IX_OrderDetailDateProdSold ON dbo.OrderDetail( OrderDate ASC, ProductID ASC, QtySold ASC );andCREATE NONCLUSTERED INDEX IX_OrderDetailDateWithProdSold ON dbo.OrderDeail(OrderDate) ASCINCLUDE (ProductID, QtySold);Fri, 30 May 2008 12:39:44 GMTtfifieldStored Procedure Inital run slow than quick each day.http://www.sqlservercentral.com/Forums/Topic1781174-360-1.aspxI have a series of SPs that run nightly. They run twice each, once building current month data, once building prior month data. Each night the first run of any given proc takes 4-8 minutes, the second run takes less than a minute. If I were to run it again it would consistently take less than a minute. The database is highly transactional, the statistics will have changed from the previous day's run. Is there anything I can do to prep the SP so that the first run is more efficient? I tried making a call to the SP with a parameter that would essentially not return any data, that still took a while. The subsequent calls with valid data producing parameters took less than a minute. I ran a trace and the CPU, reads, and writes were very similar but the duration was 5 times longer.What additional information should I be gathering in my analysis?Thanks,MylesTue, 26 Apr 2016 15:25:13 GMTMyles SigalHow to read LDF file? sql 2000http://www.sqlservercentral.com/Forums/Topic1233821-360-1.aspxHow can I read LDF file? My table is missing from database, and I want to know how it has been removed. Therefore I need to read the transactions registered in LDF fileWed, 11 Jan 2012 02:55:29 GMTmaryamzolfagharDifferences in run time for same queryhttp://www.sqlservercentral.com/Forums/Topic1775720-360-1.aspxHi allI've got a real head-thrasher on my hands.I've got a stored procedure that takes a parameter and runs an insert (I'll post the code at the bottom).If I run the base code in SSMS it takes around 30 seconds to insert the data into a table.If I run the stored procedure from a task in SSMS or Visual Studio (just a normal execute SQL task calling the stored procedure with the parameter) it takes anything up to 20 minutes.We're using all the indexes that are available to us (and we can't add any more as it's a 3rd-party application).Has anyone got any ideas as to why there's a difference?The stored procedure is here:-[code]USE [DataWarehouse_PreStaging]GO/****** Object: StoredProcedure [dbo].[usp_Load_pl_MT_APC_Episode] Script Date: 07/04/2016 09:29:25 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*=====================================================================================================================Create a process list for the DW staging update process by getting updated records from all tables used to populate theNAC Attendance tableNOTE: This procedure is to be created on the same server as the snapshots otherwise performance is severely impeded=======================================================================================================================Initials Date/Time of Change Change MadeSL 11/02/2016 Initial CodingSL 09/03/2016 Added in additional tables =======================================================================================================================Example Call: EXEC usp_Load_pl_MT_APC_Episode '20150909 00:00:00'=======================================================================================================================*/CREATE PROCEDURE [dbo].[usp_Load_pl_MT_APC_Episode] @LastUpdateDateTime AS DATETIMEASBEGIN SET DATEFORMAT dmy TRUNCATE TABLE pl_MT_APC_Episode INSERT INTO pl_MT_APC_Episode ( SourceID ,AbstractID ,LatestRowUpdateDateTime ) SELECT SourceID ,AbstractID ,MAX(RowUpdateDateTime) AS LatestRowUpdateDateTime FROM ( --We need a select statement below to get updated records --for each MT table used to populate the working table --AbstractData SELECT SourceID ,AbstractID ,RowUpdateDateTime FROM livedb_daily.dbo.AbstractData AD WHERE AD.PtStatus = 'IN' AND RowUpdateDateTime &gt; @LastUpdateDateTime --AdmPatUks UNION ALL SELECT AD.SourceID ,AD.AbstractID ,ADMPUK.RowUpdateDateTime FROM livedb_daily.dbo.AbstractData AD INNER JOIN livedb_daily.dbo.AdmPatUks ADMPUK ON AD.SourceID = ADMPUK.SourceID AND AD.VisitID = ADMPUK.VisitID WHERE AD.PtStatus = 'IN' AND ADMPUK.RowUpdateDateTime &gt; @LastUpdateDateTime --AbsServices UNION ALL SELECT AD.SourceID ,AD.AbstractID ,SER.RowUpdateDateTime FROM livedb_daily.dbo.AbstractData AD INNER JOIN livedb_daily.dbo.AbsServices SER ON AD.SourceID = SER.SourceID AND AD.AbstractID = SER.AbstractID WHERE AD.PtStatus = 'IN' AND SER.RowUpdateDateTime &gt; @LastUpdateDateTime --AbsPatUkConsultantEpisodes UNION ALL SELECT AD.SourceID ,AD.AbstractID ,EP.RowUpdateDateTime FROM livedb_daily.dbo.AbstractData AD INNER JOIN livedb_daily.dbo.AbsPatUkConsultantEpisodes EP ON AD.SourceID = EP.SourceID AND AD.AbstractID = EP.AbstractID WHERE AD.PtStatus = 'IN' AND EP.RowUpdateDateTime &gt; @LastUpdateDateTime --MisSpec_Main & MisSpec_Codes (TFC) UNION ALL SELECT AD.SourceID ,AD.AbstractID ,RowUpdateDateTime = ( SELECT CASE WHEN MAX(TREATFUNC.RowUpdateDateTime) &gt; MAX(TREATFUNCTC.RowUpdateDateTime) THEN MAX(TREATFUNC.RowUpdateDateTime) ELSE MAX(TREATFUNCTC.RowUpdateDateTime) END FROM livefocdb_daily.dbo.MisSpec_Main TREATFUNC LEFT JOIN [MEDITECHDR01-M1].livefocdb_daily.dbo.MisSpec_Codes TREATFUNCTC ON TREATFUNC.SourceID = TREATFUNCTC.SourceID AND TREATFUNC.MisSpecID = TREATFUNCTC.MisSpecID AND TREATFUNCTC.SubmissionType_MisSubmTypeID = 'NHS CDS' WHERE TREATFUNC.SourceID = ADMPUK.SourceID AND TREATFUNC.MisSpecID = ADMPUK.SubSpecialty ) FROM livedb_daily.dbo.AbstractData AD INNER JOIN livedb_daily.dbo.AdmPatUks ADMPUK ON AD.SourceID = ADMPUK.SourceID AND AD.VisitID = ADMPUK.VisitID WHERE AD.PtStatus = 'IN' AND AD.AdmitDateTime IS NOT NULL AND ADMPUK.SubSpecialty IN ( SELECT TREATFUNC.MisSpecID FROM livefocdb_daily.dbo.MisSpec_Main TREATFUNC LEFT JOIN [MEDITECHDR01-M1].livefocdb_daily.dbo.MisSpec_Codes TREATFUNCTC ON TREATFUNC.SourceID = TREATFUNCTC.SourceID AND TREATFUNC.MisSpecID = TREATFUNCTC.MisSpecID AND TREATFUNCTC.SubmissionType_MisSubmTypeID = 'NHS CDS' WHERE TREATFUNC.RowUpdateDateTime &gt; @LastUpdateDateTime OR TREATFUNCTC.RowUpdateDateTime &gt; @LastUpdateDateTime ) --MisSvc_Main & MisSvc_Codes (Specialty) UNION ALL SELECT AD.SourceID ,AD.AbstractID ,RowUpdateDateTime = ( SELECT CASE WHEN MAX(MAINSPEC.RowUpdateDateTime) &gt; MAX(MAINSPECTC.RowUpdateDateTime) THEN MAX(MAINSPEC.RowUpdateDateTime) ELSE MAX(MAINSPECTC.RowUpdateDateTime) END FROM livefocdb_daily.dbo.MisSvc_Main MAINSPEC LEFT JOIN [MEDITECHDR01-M1].livefocdb_daily.dbo.MisSvc_Codes MAINSPECTC ON MAINSPEC.SourceID = MAINSPECTC.SourceID AND MAINSPEC.MisSvcID = MAINSPECTC.MisSvcID AND MAINSPECTC.SubmissionType_MisSubmTypeID = 'NHS CDS' WHERE MAINSPEC.SourceID = SER.SourceID AND MAINSPEC.MisSvcID = SER.AbsServiceID ) FROM livedb_daily.dbo.AbstractData AD INNER JOIN livedb_daily.dbo.AbsServices SER ON AD.SourceID = SER.SourceID AND AD.AbstractID = SER.AbstractID WHERE AD.PtStatus = 'IN' AND SER.AbsServiceID IN ( SELECT MAINSPEC.MisSvcID FROM livefocdb_daily.dbo.MisSvc_Main MAINSPEC LEFT JOIN [MEDITECHDR01-M1].livefocdb_daily.dbo.MisSvc_Codes MAINSPECTC ON MAINSPEC.SourceID = MAINSPECTC.SourceID AND MAINSPEC.MisSvcID = MAINSPECTC.MisSvcID AND MAINSPECTC.SubmissionType_MisSubmTypeID = 'NHS CDS' WHERE MAINSPEC.RowUpdateDateTime &gt; @LastUpdateDateTime OR MAINSPECTC.RowUpdateDateTime &gt; @LastUpdateDateTime ) ) UpdatedRecords GROUP BY SourceID ,AbstractIDENDGO[/code]I've had a look at the Activity Monitor while the procedure is running in both scenarios and they appear to be identical. I've even tinkered with the Cost Threshold for Parallelism but it doesn't seem to make any difference.All we want to do is get the details of any record that has changed from any of the above tables since the last load (hence the parameter).Any help gratefully received.::edit::I've just added the execution plan as well.::edit2::Just noticed an error in my original post so updated it.Thu, 07 Apr 2016 02:33:34 GMTrichardmgreen1Creating table from 3 diffrent tables with conditionshttp://www.sqlservercentral.com/Forums/Topic1773344-360-1.aspx1) Assuming Process, Organization and Department tables all exist. please show me how you would create table(s) as it relates to these 3 existing tables/keys (OrganizationID, DepartmentID and ProcessID); Facts: In each organization there can be 1 or more departments and within each department there can be 1 or more processes.2) Status lookup values are configured by organization/departments3) Status lookup values created by the organization/department are then assign to different Process4) Be able to inactivate or activate these dropdown valuesTue, 29 Mar 2016 15:39:01 GMT2smegkMissed hostnames and logins in SQL Auditinghttp://www.sqlservercentral.com/Forums/Topic744227-360-1.aspxWe are running SQL Auditing which captures hostname, login ids,applicationname, servername etc. Today we found some activities captured but hostname , applicationname are missing . How to get those missed hostname and application after auditing was run ?Tue, 30 Jun 2009 02:25:53 GMTSSRfromChennaiData Profiling using T-SQL querieshttp://www.sqlservercentral.com/Forums/Topic1739113-360-1.aspxHi Please help, I am new on SQL, I need all the t-sql queries that can get me started with data profiling. Your help will be appreciated.Thanks.Mon, 23 Nov 2015 02:15:13 GMTCeliweMDerived table in linked server query behaving as a subquery -- 1 execution per rowhttp://www.sqlservercentral.com/Forums/Topic1731585-360-1.aspxI have a stored procedure that builds out a table for reporting purposes. The procedure runs every 5 minutes to completely rebuild the table, as it needs to be 'nearly' real time data. The total rows are usually ~6,000, and because it is a bit complicated to pull all the information together it takes about 5 seconds or so to complete. The snip below is the final part, where we refer to our warehouse management system to identify any orders that have not been shipped so we can delete those rows. The portion in question is the derived table:[code="sql"] ( select orderid COLLATE SQL_Latin1_General_CP1_CI_AS AS Orderid ,custid COLLATE SQL_Latin1_General_CP1_CI_AS AS Custid ,shipdate FROM [LINKEDSERVER].wms.wms.orders so )[/code]This is a straightforward derived table, that returns about 30K rows. It is narrow and fairly concise, given the nature of things. It runs sub-second, and when run locally on its own server it results in about 10K reads. I expect this to behave as a 'table' (derived table) that the outer query can join to as we normally expect.The overall statistics for the entire query below is 172 logical reads and it takes about 1 second. The logical reads is what I would expect, as there are ~6K rows that need to be scanned.[code="sql"] DELETE dbo.ReportingOrders WHERE orderid IN ( SELECT t.orderid FROM ( SELECT 'HOP'+REPLICATE('0', 8 - LEN(orderid)) + CAST(orderid AS varchar) as 'NumberFull' ,scheduleddate_shipping ,custnmbr ,ORDERID FROM [dbo].[ReportingOrders] WHERE OrderType IN ('Not Shipped, No Shipment','Not Shipped, With Shipment') ) t JOIN ( select orderid COLLATE SQL_Latin1_General_CP1_CI_AS AS Orderid ,custid COLLATE SQL_Latin1_General_CP1_CI_AS AS Custid ,shipdate FROM [LINKEDSERVER].wms.wms.orders so ) s ON t.custnmbr=s.custid AND t.numberfull=s.orderid AND t.scheduleddate_shipping=s.shipdate )[/code]HOWEVER, our database monitoring software has singled this out as a VERY resource intensive query. We did a trace, and it appears that the derived table is in fact being executed 1x for each of the 6K rows, returning a total of 220,000,000 rows !!!!To test if this is actually the case, I rewrote the query as follows:[code="sql"] DECLARE @d DATETIME SET @d = DATEADD(dd,-60,GETDATE()) select orderid COLLATE SQL_Latin1_General_CP1_CI_AS AS Orderid ,custid COLLATE SQL_Latin1_General_CP1_CI_AS AS Custid ,shipdate INTO #b FROM [LINKEDSERVER].wms.wms.orders so WHERE SHIPDATE&gt;@d DELETE dbo.ReportingOrders WHERE orderid IN ( SELECT t.orderid--,s.* FROM ( SELECT 'HOP'+REPLICATE('0', 8 - LEN(orderid)) + CAST(orderid AS varchar) as 'NumberFull' ,scheduleddate_shipping ,custnmbr ,ORDERID FROM [dbo].[ReportingOrders] WHERE OrderType IN ('Not Shipped, No Shipment','Not Shipped, With Shipment') ) t JOIN #b /*( select orderid COLLATE SQL_Latin1_General_CP1_CI_AS AS Orderid ,custid COLLATE SQL_Latin1_General_CP1_CI_AS AS Custid ,shipdate FROM [LINKEDSERVER].wms.wms.orders so )*/ s ON t.custnmbr=s.custid AND t.numberfull=s.orderid AND t.scheduleddate_shipping=s.shipdate )DROP TABLE #b[/code]Sure enough, this resulted in the query completely disappearing from the list of 'resource intensive queries', and of course it also runs faster. My takeaway from this is that derived tables in queries that use linked servers are handled COMPLETELY differently by the optimizer. We tend to use this quite often, so it will mean a lot of code review. I also wonder if linked server queries in general simply do not operate by normal sql server rules. Does anyone have any experience with issues like this? TIA d lewisWed, 28 Oct 2015 09:09:49 GMTDavidLCreating Clustered index on a large tablehttp://www.sqlservercentral.com/Forums/Topic945800-360-1.aspxI have a fairly large table with roughly 120 million rows. It has a primary key composed of 3 integer fields but, its non-clustered :(Now, to improve performance on certain queries we would like to cluster the primary key. Is there a quick and efficient way of doing it without causing much downtime? The table is an archive and doesnt get accessed very frequently in the normal day to day operations.Thanks in advance for any help provided.RegardsWed, 30 Jun 2010 14:59:27 GMTanish_nsPercentage of Fragmentation increased after 6 hourshttp://www.sqlservercentral.com/Forums/Topic1711694-360-1.aspxGood morning,Yesterday night, I rebuilded all indexes one a table since they were having high percentage of fragmentation . After rebuilding, I immediately checked the fragmentation percentage and it got reduced and I felt happy. However, when i saw the percentage of fragmentation after 6 hours, it increased from 1% to 48%. Please help on how to find what caused the increase and how to fix it. Thanks is advanceFri, 14 Aug 2015 01:04:11 GMTcoolchaituMonitoring for SQL server performance issueshttp://www.sqlservercentral.com/Forums/Topic1718754-360-1.aspxHello all, I'm new to SQL Server management and i'm trying to setup some monitoring and establish a baseline. I have noticed some people using SQL queries to get values from SQL's performance counters and others using windows performance monitor. When comparing these, specifically, SQLServer:Buffer Manager - Page reads/sec and page writes/sec. SQL Performance data gives High values and yet in windows performance monitor it shows 0.00. That makes me a little confused. So should I use values from SQL Server performance using a query or should I be using windows performance monitor, and why is there a difference?(I have attached a screenshot.)these are the counters I am planning to use.OBJECT COUNTERMemory – Available MBytesMemory - Pages/secPaging File – % UsagePhysical Disk – Avg. Disk sec/ReadPhysical Disk – Avg. Disk sec/WritePhysical Disk – Disk Reads/secPhysical Disk – Disk Writes/secPhysical Disk - % Disk Time (add all physical disks not total)Processor – % Processor Time (add all processors not total)SQLServer: Buffer Manager – Buffer Cache Hit RatioSQLServer: Buffer Manager – Buffer Cache Hit Ratio BaseSQLServer: Buffer Manager – Page Lookups /SecSQLServer: Buffer Manager – Free PagesSQLServer: Buffer Manager – Lazy Write/secSQLServer: Buffer Manager – Page Reads/secSQLServer: Buffer Manager – Page Writes/secSQLServer: Buffer Manager – Page life expectancySQLServer: Locks - Number of Deadlocks/sec (total)SQLServer: Database - Transactions/sec (total)SQLServer: SQL Statistics – Batch Requests/secSQLServer: SQL Statistics – Compilations/secSQLServer: SQL Statistics – Recompilations/secSQLServer: General Statistics – User ConnectionsSQLServer: Memory Manager – Memory Grants PendingSQLServer: Memory Manager – Target Server Memory(KB)SQLServer: Memory Manager – Total Server memory (KB)System – Processor Queue LengthFri, 11 Sep 2015 07:29:59 GMTThompsonAdminBlocking due to access_methods_scan_range_generator and Latch_Exhttp://www.sqlservercentral.com/Forums/Topic1715947-360-1.aspxGood morning Everyone,Blocking is happening due to access_methods_scan_range_generator and Latch_Ex. I see several multi-threaded sessions blocking itself. I looked into it but unable to find solution. I have attached the screenshot.Could you please help.Tue, 01 Sep 2015 05:11:19 GMTcoolchaituHuge difference between estimated rows and actual rowshttp://www.sqlservercentral.com/Forums/Topic1713635-360-1.aspxGood morning,There is a stored procedure. It uses linked server. As we will be migrating to amazon cloud, our architect instructed not to replace linked server with openquery. Please find the execution plan details attached. Please help.Fri, 21 Aug 2015 04:47:06 GMTcoolchaituLong running Select Queryhttp://www.sqlservercentral.com/Forums/Topic1712541-360-1.aspxGood morning,The below query is running for long time:Tue, 18 Aug 2015 06:20:17 GMTcoolchaituA stored procedure that usually runs fast ran for a long time yesterdayhttp://www.sqlservercentral.com/Forums/Topic1705438-360-1.aspxAn ADO.NET application calls a stored procedure. The response time is usually fast. However, yesterday the response time was very slow. And it caused timeout errorsI checked activity monitor and there was blocking with wait type PAGELATCH_UP and LATCH_EX as WaitType. Please find the screenshot attachedFri, 24 Jul 2015 02:57:09 GMTcoolchaituSelect query in a stored procedure running for long timehttp://www.sqlservercentral.com/Forums/Topic1707593-360-1.aspxWe have a stored procedure.It has a select query and its taking long time.I have attached the image of select query,image of actual execution plan and image of index scan details.The index scan is costing 80%Please help on what to do so that the query gets tuned.Fri, 31 Jul 2015 12:37:01 GMTcoolchaituQuery inside an IF statement runs foreverhttp://www.sqlservercentral.com/Forums/Topic1706629-360-1.aspxHi,I came upon an odd issue today:I have a query that runs fine and at an acceptable speed (a few seconds). It looks like this:select count(row_id) From table1 v inner join (select name,max(somedate) as correct_date From table1 group by name ) zon v.name = z.name and correct_date != somedateThis query is a part of a stored procedure where it is used as a condition of an IF statement. That part of the SP is extremely slow.Below is a query that ran for an hour until i stopped it:if ((select count(row_id) From table1 v inner join (select name,max(somedate) as correct_date From table1 group by name ) zon v.name = z.name and correct_date != somedate) ) &gt; 0beginselect 1endCan anyone suggest an explanation for this behaviour? I used a local variable to calculate the result of the query before the IF statement and that solved the problem, though i feel like this might me sidestepping the real issue.Wed, 29 Jul 2015 06:51:30 GMTtomullus 69522Duplicate Index...or am I missing something?http://www.sqlservercentral.com/Forums/Topic1689309-360-1.aspxI inherited a mess and now I'm trying to clean it up. It appears like many indexes were automatically created by the DTA which is making me cringe. I am by no means an expert on Indexes but I'd like to think I have a pretty decent grasp of things. So I have a situation here where I was just about to disable a couple of indexes I thought were duplicate but thought I'd better at least give it a mention. It certainly wouldn't be the first time I would have missed something.Clustered Index -&gt; ID, Version (both keys)Non Clustered Index 1 -&gt; ID, Version + 1 Included ColumnNon Clustered Index 2 -&gt; ID, Version + 6 Included Columns (including included column from Index 1)I didn't even list the names of the included columns because as far as I can see they don't even matter. All three are indexes are keyed on the exact same two columns in the exact same order. I also know that it's possible for the optimizer to use duplicate indexes so usage doesn't give you the full picture.Before I disable the two Non Clustered Index does anybody know why it would ever make sense to create them in the first place?Wed, 27 May 2015 13:16:24 GMTY.B.SID Makes Query Slow in Clustered Column store? Execution Plan increased though CPU Time came down.http://www.sqlservercentral.com/Forums/Topic1701303-360-1.aspxQuery 1---------Business Key in Both Tables so join on Business KeyDBCC DROPCLEANBUFFERSDBCC FREEPROCCACHESELECT DIM_MATERIAL.MATNR, DIM_MATERIAL.MAKTX, sum(F_BILLING.NETWR_1)FROM DIM_MATERIAL INNER JOIN F_BILLING ON (DIM_MATERIAL.MATNR=F_BILLING.MATNR) GROUP BY DIM_MATERIAL.MATNR, DIM_MATERIAL.MAKTX order by DIM_MATERIAL.MATNR SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 12 ms.DBCC execution completed. If DBCC printed error messages, contact your system administrator. SQL Server Execution Times: CPU time = 125 ms, elapsed time = 119 ms.DBCC execution completed. If DBCC printed error messages, contact your system administrator. SQL Server Execution Times: CPU time = 16 ms, elapsed time = 10 ms.(21513 row(s) affected)Table 'F_BILLING'. Scan count 8, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 14540, lob physical reads 1, lob read-ahead reads 7889.Table 'DIM_MATERIAL'. Scan count 9, logical reads 2992, physical reads 2, read-ahead reads 2712, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.(1 row(s) affected) SQL Server Execution Times: CPU time = 345 ms, elapsed time = 491 ms.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.Query 2---------Surrogate Key in Both Tables so join on Surrogate IDDBCC DROPCLEANBUFFERSDBCC FREEPROCCACHESELECT A.MATNR, A.MAKTX, SUM(B.[NETWR_1])FROM [dbo].[DIM2_MATERIAL] A INNER JOIN [dbo].[F_BILLING2] B ON A.MATNR_ID=B.MATNR_IDGROUP BY A.MATNR, A.MAKTXorder by A.MATNRSQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 15 ms.DBCC execution completed. If DBCC printed error messages, contact your system administrator. SQL Server Execution Times: CPU time = 110 ms, elapsed time = 113 ms.DBCC execution completed. If DBCC printed error messages, contact your system administrator. SQL Server Execution Times: CPU time = 15 ms, elapsed time = 8 ms.(21514 row(s) affected)Table 'F_BILLING2'. Scan count 8, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 8547, lob physical reads 3, lob read-ahead reads 4173.Table 'DIM2_MATERIAL'. Scan count 9, logical reads 837, physical reads 3, read-ahead reads 764, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.(1 row(s) affected) SQL Server Execution Times: CPU time = 174 ms, elapsed time = 28495 ms.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.Both Fact tables are Clustered Column Store.Wed, 08 Jul 2015 16:58:39 GMTWest822Stored Proc vs Inline Querieshttp://www.sqlservercentral.com/Forums/Topic1697383-360-1.aspxHi ,We have a performance issue on one of the queries. We have observed some potential improvements in the queries. Currently its an inline query in the .net code.My question: Moving the as is inline query to a SP will help in any performance gains?.Wed, 24 Jun 2015 09:29:08 GMTGonnaCatchITTo create login in many servers.http://www.sqlservercentral.com/Forums/Topic893538-360-1.aspxHi, Its an interview question.How to create a login in 500 servers at one shot????Wed, 31 Mar 2010 02:20:52 GMTSaravanan T Certain Value In Where Clause Hangs Query For 5 Minuteshttp://www.sqlservercentral.com/Forums/Topic1677117-360-1.aspxI have waited a couple weeks to post this because it's going to sound ridiculous, and because I can't really provide the data here to recreate the problem due to it being sensitive. But I'm desperate and just have to ask...Basically, my company uses a program called IBM Cognos ICM, which is sort of a glorified front-end for SQL Server (2005 in our case). In this case it is being used to generate a web report which our employees use. The web report has dropdowns for the user to select a month and a costcenter. It works perfectly fine except for one costcenter. If the user selects that costcenter and ANY month, the report takes about 5 minutes to render its paltry 4 rows, compared with a couple seconds for any other costcenter. But it gets weirder. Not that the specifics probably matter, but the costcenter that caused problems 2 weeks ago was 21155. It exhibited this behavior for a couple days. Then was magically fixed for a couple days. Then came back. Then was fixed again. Today we discovered that a new costcenter, 00966, is exhibiting this behavior. I had our DBA run SQL Profiler while the report was hanging up so we could capture the SQL that's being run behind the scenes. So I've been able to recreate the problem using pure SQL in all 4 of our environments (different servers). FYI, the DBA also confirmed that all our databases have weekly maintenance performed to rebuild indexes, etc. I understand that none of you will be able to test this code for me at all, but I'll include it here on the off chance someone recognizes something familiar that has tripped you up in the past. I have cleaned this up as much as possible. The problem seems to lie in the WHERE clause. If I change the '00966' to any other value, it works fine. If I change the '2015, Month 03' value to any INVALID value (any value that would find no matches) it works fine. But the combination of '00966' and any valid month value hangs it up. Also, removing all the JOINS resolves the problem. So I've removed as many as I can but left the ones that are necessary to recreate the problem. [code="sql"]SELECT *FROM ( SELECT [_Port995].[Months],[_Port995].[CostCenter],[_Port995].[Tier],[_Port995].[MIPCategory],[_Port995].[Value_],[_I1].[ReportDisplay] AS [_Column1] FROM (SELECT [CostCenter],[Tier],[MIPCategory],[MIPSubcategory],[Months],[Value_] FROM [_Result964]) [_Port995] INNER JOIN [MIPSubcategoryConstants] [_I1] ON [_Port995].[MIPSubcategory] = [_I1].[MIPSubcategory] ) [_Port995] INNER JOIN (SELECT [TimeID_],[Name_],[Ending_],[Starting_] FROM [Time_]) [_E1] ON [_E1].[TimeID_] = 'CAL' AND [_E1].[Name_] = [_Port995].[Months] INNER JOIN (SELECT [TimeID_],[Level_],[Starting_],[Ending_],[Name_] FROM [Time_]) [_E2] ON [_E2].[TimeID_] = 'CAL' AND [_E2].[Level_] = 'Quarters' AND [_E2].[Starting_] &lt;= [_E1].[Ending_] AND [_E2].[Ending_] &gt;= [_E1].[Starting_] INNER JOIN (SELECT [TimeID_],[Level_],[Starting_],[Name_] FROM [Time_]) [_E5] ON [_E5].[TimeID_] = 'CAL' AND [_E5].[Level_] = 'Months' AND [_E5].[Starting_] = [_E2].[Starting_] INNER JOIN (SELECT [TimeID_],[Level_],[Ending_],[Name_] FROM [Time_]) [_E6] ON [_E6].[TimeID_] = 'CAL' AND [_E6].[Level_] = 'Months' AND [_E6].[Ending_] = [_E2].[Ending_] INNER JOIN (SELECT [Date],[PrevMonth] FROM [DateString]) [_E7] ON [_E7].[Date] = [_E2].[Ending_] INNER JOIN (SELECT [TimeID_],[Name_] FROM [Time_]) [_E8] ON [_E8].[TimeID_] = 'CAL' AND [_E8].[Name_] = [_E7].[PrevMonth] INNER JOIN ( SELECT [CostCenter],[Months],[Tier],[Value_] FROM ( SELECT [CostCenter],[Tier],[MIPCategory],[MIPSubcategory],[Months],[Value_] FROM [_Result188] ) [_E9] ) [_E9] ON [_E9].[CostCenter] = [_Port995].[CostCenter] AND [_E9].[Months] = [_E5].[Name_] AND [_E9].[Tier] = [_Port995].[Tier] INNER JOIN ( SELECT [CostCenter],[Months],[Tier],[Value_] FROM ( SELECT [CostCenter],[Tier],[MIPCategory],[MIPSubcategory],[Months],[Value_] FROM [_Result188] ) [_E10] ) [_E10] ON [_E10].[CostCenter] = [_Port995].[CostCenter] AND [_E10].[Months] = [_E8].[Name_] AND [_E10].[Tier] = [_Port995].[Tier] INNER JOIN ( SELECT [CostCenter],[Months],[Tier],[Value_] FROM ( SELECT [CostCenter],[Tier],[MIPCategory],[MIPSubcategory],[Months],[Value_] FROM [_Result188] ) [_E11] ) [_E11] ON [_E11].[CostCenter] = [_Port995].[CostCenter] AND [_E11].[Months] = [_E6].[Name_] AND [_E11].[Tier] = [_Port995].[Tier] WHERE [_Port995].[CostCenter] = '00966' AND [_Port995].[Months] = '2015, Month 03' [/code](Sorry it's not very pretty here, it should paste nicely into SSMS.)Tue, 14 Apr 2015 14:17:22 GMTautoexcrementLog File Managementhttp://www.sqlservercentral.com/Forums/Topic1678225-360-1.aspx1. my Production log file is 59 GB. and My data file is 44 GB. It seems Log file is larger than Data file. I do regularly Full database Backup and Transaction log file backup by maintenance plan. After full database and transaction log file backup Log Space Used (%) becomes around 0.123%. Can i shrink log file or truncate log file?Is there is any drawbacks of shrinking log file to 2 MB some thing like that? If log file size is large even though there is regular full database and transaction log backup does it effect in Performance?Could you please suggest me and give me suggestion for me further steps?2. My system database 'Master' has 85% Log space Used and log size MB is 10 MB. Could some one please suggest me what to do further?Sat, 18 Apr 2015 21:50:28 GMTkeshab.basnetQuestion to about optimizationhttp://www.sqlservercentral.com/Forums/Topic1677880-360-1.aspxOriginal QUery to optimize:SELECT FC.cexrate, FC.cagrate, FC. forex_tag_no, FC.parent_agent_cd AS CollectingAgentCd, FC.paying_agent_cd AS PayingAgentCd FROM (SELECT c.ex_rate AS cExRate, c.agent_rate AS cAgRate, c.forex_tag_no, c.parent_agent_cd, c.paying_agent_cd, Row_number() OVER( partition BY c.forex_tag_no, c.paying_agent_cd ORDER BY c.created_on DESC) AS Row FROM dbo.forex_coll c WHERE c.parent_agent_cd = 'MY0001' ) FC WHERE FC.row = 1 Could you please suggest above query for optimization and suggest what and how index could be used to optimize above query?Thu, 16 Apr 2015 23:20:24 GMTkeshab.basnetLEFT function at where clause criteriahttp://www.sqlservercentral.com/Forums/Topic1676371-360-1.aspxHi Everyone,Its not a good idea to use functions at where clause, instead of seek, query plan will choose index scan..Ex:SELECT EmailAddress FROM person.contact WHERE left(EmailAddress,2) = 'As'Sub-tree cost : 20.8989I have changed the query to below SELECT EmailAddress FROM person.contact WHERE EmailAddress like 'As%'Subtree cost: 4.89000sub-tree cost is 0.0032 if i use below querySELECT EmailAddress FROM person.contact WHERE EmailAddress = 'As' ...I know i can't use this query either i have to use left function or like keyword to searh email address..is there any we can reduce sub tree cost of query ?...!Thanks for your help..Sat, 11 Apr 2015 09:57:13 GMTkoti.raavi