Tuesday, April 16, 2013

[SQL 2012] PK Lookup Doing Table Scan?

[SQL 2012] PK Lookup Doing Table Scan?

PK Lookup Doing Table Scan?

Posted: 16 Apr 2013 02:06 AM PDT

I had an issue with replication that I posted in the replication forum, but I don't think it is specific to replication as much as SQL2012 and indexing issues. Here is the other post, not sure if anyone in this forum has experienced this before?[url]http://www.sqlservercentral.com/Forums/Topic1442423-291-1.aspx[/url]Basically, the merge process that enumerated changes in batches (sp_MSenumchanges_belongtopartition) executed a query to join the rowguid of the changes to the PK/rowguid in my user table. This should have been a super fast query to seek the clustered index of my user table, but instead looking up a 100 row batch was taking around 10 minutes (sometimes significantly longer) and pinning the CPU. If I ran the same query manually, outside of sp_MSenumchanges_belongtopartition, it would return in a matter of milliseconds.Index usage stats looked like sp_MSenumchanges_belongtopartition was executing a scan on the table, instead of doing a clustered index seek like it would if I executed the query manually. I have no idea why the SP would be executed such a bad query plan, and not sure how to confirm that was the issue or how to more-permanently resolve it?For now, I created a second nonclustered index on the PK, effectively duplicating the clustered index. Now replication is fine and sp_MSenumchanges_belongtopartition executes in <1s. When I look at the query plan, it shows sp_MSenumchanges_belongtopartition using my new index to locate the PK of the row. Then it joins that result to a clustered index seek to pull the whole row of data. So it's not as efficient as just a clustered index seek, but is working.Any ideas? Index corruption a possibility? What should I look at next?

SQL Query - Actual performance duration for every execution

Posted: 15 Apr 2013 10:14 PM PDT

Hi,I have a SQL query with couple of nested queries (query at the bottom for the reference), now the very first time I execute the query on a SQL Server instance, it takes around 50-60 seconds to execute, but there on for every consecutive execution, it barely takes 1 second and I am baffled, because I want it to take its original time for me to do anything about reduction of its original execution time. I want to try various tuning and query modification aspects.I have tried clearing all caches using the commands listed underneath, but they does not help in getting the original execution time.We are using "SQL Server 2012 Enterprise Edition"use <Database_Name>GOCHECKPOINT; GO DBCC FLUSHPROCINDB(<Database_ID>)DBCC FREEPROCCACHEDBCC DROPCLEANBUFFERSDBCC FREESYSTEMCACHE ('ALL')DBCC FREESESSIONCACHEGONormally anyone would be happy that query is performing great after first execution but my aim is different, I want it to take actual time for every consecutive executionPlease help me to resolve the issue mentioned above, any pointer would be great, please let me know if you need more detailsthanks,MrinalQuery Text for Reference=======================SELECT OprtngOrigHandlingNodeID, OprtngDestHandlingNodeID, ShpmtOrigHandlingNodeID, ShpmtDestHandlingNodeID, CAST(COUNT(*) as real)/ (SELECT ABS(DATEDIFF(d,'2013-02-01','2013-02-28'))+1 as DaysInRange) AS carsFROM ( select distinct S.* from RBIS_UI.Trip_Plan TP, RBIS_UI.combined_shipments S, ( SELECT * FROM ( SELECT T.TrainRunID, T.Rundate, T.TrainRunCode, T.Distance, T.Speed, T.Cars, T.InterModal, T.Length, T.Weight, T.WorkStops, T.NonDesignStops, T.HP, T.HPTT, T.ActLocoCount, T.DeactLocoCount, T.TotalLocoCount, T.DepDesignTimeDiff, T.ArrDesignTimeDiff, T.TransitTime, T.DesignTransitTime, T.LocalArrtime AS Arrtime, T.LocalDeptime AS Deptime, T.LocalDepSchedTime as DepSchedTime, T.LocalArrSchedTime as ArrSchedTime, T.DepDelay, T.ArrDelay, T.titanID, T.tyesID, T.CorCapacity, COALESCE (dt_TrainName, trainname) as trainname, COALESCE (dt_OpsTypeId, OpsTypeID) as OpsTypeID, COALESCE (dt_BizType, BizType) as BizType, COALESCE (dt_ActualOrigHandlingNodeID, ActualOrigHandlingNodeID) as ActualOrigHandlingNodeID, COALESCE (dt_ActualDestHandlingNodeID, ActualDestHandlingNodeID) as ActualDestHandlingNodeID, ActualOrigHandlingNodeID as Actual_ActualOrigHandlingNodeID, ActualDestHandlingNodeID as Actual_ActualDestHandlingNodeID, DesignOrigHandlingNodeID, DesignDestHandlingNodeID, OpsTypeID as Actual_OpsTypeID, BizType as Actual_BizType, TrainName as Actual_TrainName, T.DesigntrainID, DETOURFLAG, Via, CarsLoaded, IntermodalLoaded, CarsEmpty, IntermodalEmpty, Addition -- , -- DepDesignTimeDiff, --ArrDesignTimeDiff FROM ( SELECT * FROM ( SELECT * FROM RBIS_UI.train_runs WHERE RunDate BETWEEN '2013-02-01' AND '2013-02-28') FT WHERE OpsTypeID IN ( 1,2,3,5,6,9 ) ) T) TR WHERE ((TR.TrainName = '470')) AND ((TR.ActualOrigHandlingNodeID = 1190)) AND ((TR.ActualDestHandlingNodeID = 504)) AND ((TR.DesignTrainID = 1901)) AND ((TR.OpsTypeID = 3)) AND ((TR.BizType = 'SECO'))) TR where TR.TrainRunID = TP.TrainRunID and TP.CSN = S.CycleSerialID and TP.Shipment_ReleaseDate = S.ReleaseDate AND TP.EqpmtType <> 2 ) FSGROUP BY GROUPING SETS ( (OprtngOrigHandlingNodeID), (OprtngDestHandlingNodeID), (ShpmtOrigHandlingNodeID), (ShpmtDestHandlingNodeID) )

SQL 2012 AG/FCI Failover

Posted: 15 Apr 2013 06:43 PM PDT

Hi AllMy Setup:>> 3 Servers (Node1-Node2-Node3) all part of the same Cluster "Cluster1">> SQL installed as clustered on Node2 & Node3, producing "SQLCluster">> SQL installed as nonclustered on Node1, producing "Node1\SQL">> Availability Group has been set up between "SQLCluster" & "Node1\SQL"Quorum: What is the recommended Quorum configuration for my setup, I'm assuming Node majority.DTC: Should all the nodes be a possible owner of DTC? Should the DTC disk be presented to all the nodes?Failover: When configuring the failover mode, I am unable to change it to Automatic, is Automatic failover not supported when you combine an AG with an FCI or am I missing something?Thanks

Problem SSIS 2012 using Teradata Destination

Posted: 15 Apr 2013 07:39 PM PDT

Hi,I installed Attunity SSIS Terdata Adapter Version 2.0 on my machine (SQL Server 2012 EE), downloaded from the Microsoft page.I tried just an easy File Connection reading a CSV File to import it into a Teradata (14.03) using the Teradata Destination component. The error "[b]The Teradata TPT registry key cannot be opened. Verify that the TPT API 12.0 or 13.0 Edition 2 (13.0.0.2) for Windows x86 is installed properly." [/b]was thrown by SSIS.I have installed the TPT 14.00 on my machine, and the version 2.0 of the Teradata Destination should work with TPT 14.00 and SQL Server 2012 (EE)?!Does anyone has an idea how to solve that?Thanks in advanceHelmut

Cannot connect using mixed authentication

Posted: 15 Apr 2013 12:53 PM PDT

We are attempting to deploy a SQL Server Express 2012 application in a network using Workgroup networking, and as a result are having to use SQL Server logins rather than Integrated security. All works as it should when connected to the PC running SQL Server. However when we attempt to connect from another Win7 workstation using ODBC, we either get timeout errors, or we get the message that SQL Server actively rejected our connection attempt (we are using Native Client 11 as the ODBC driver, both on the SQL Server PC, and on the network PC). We are also getting Error 8 - "The SQL Server Browser service was unable to process a client request" logged in the Application Log on the SQL Server PC in some attempts, but not in others. Any suggestions on what might be causing the problem? We've done this sort of configuration previously using SQL Server Express 2008 and gotten it to work, but we been banging our heads on this for 3 weeks now, and exhausted all of our usual bag of tricks. Thanks in advance.Wendell

SQL Server Port Number Assignments - How To Determine What Number To Use

Posted: 15 Apr 2013 06:57 AM PDT

Google "SQL Server Static Ports" or any variation of it and you'll get a hundreads of thousands or more pages full of links and everyone will provind emost of what you need. If you query is instead "How to Determine WHAT Port Number TO use" then forget about it, you get nothing. I've found more articles then I can count on how to change SQL Server from dynamic port to fixed ports but not one on how to determine what port number to assign. I know there are lists of port numbers used by certain apps and services but that still doesn;t help me to deterine what poirt numbers I can or even should use for SQL Server. Do I just randomly piece together a set of 6 dgits or do I need to select from a specifi range and if yes what range and how do I detemrine if a port number is OK to use?Perhaps this ia dumb question that everyone knows but me and thats why theres no hits for this query?We're setting up Syetm Center 2012 and we have no choice but to go with Static ports. Does anyone have any suggestions on how I go about dertermining what post number(s) I can use/try?Thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog