Thursday, August 22, 2013

[SQL 2012] "cpu drift" possibly causing 15 second IO messages in log?

[SQL 2012] "cpu drift" possibly causing 15 second IO messages in log?

"cpu drift" possibly causing 15 second IO messages in log?

Posted: 22 Aug 2013 02:35 AM PDT

We've seen messages like the one below for years, on and off. Usually during index maintenance. Looking for feedback from others who may have found a solution.[b]SQL Server has encountered 878 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [K:\tempdb_data3\tempdev3.mdf[/b]We know our average read/write wait times are generally below 50 milliseconds (tempdb does show 1.2 seconds average write wait, no doubt skewed by updating stats on large tables with 80% sample rate ).Our production sql cluster is currently running on 40 out of 64 cores until we upgrade the licensing model, but we've seen these messages in the log long before we upgraded to sql 2012 from 2005.We also just switched the servers power config to run in performance mode, not energy savings ( no sql restart or reboot ). The 15-second messages still occur once in a while.http://blogs.msdn.com/b/psssql/archive/2006/11/27/sql-server-2005-sp2-will-introduce-new-messages-to-the-error-log-related-to-timing-activities.aspx

Left join with duplicate records

Posted: 22 Aug 2013 01:55 AM PDT

Hi all,I am doing a left join betwen two tables, the issue is that the result of this join is bringing more rows that he left table has, from what i saw in the internet the issue resides on the right table which is making the left table adding some duplicates.Now how can i solve this??? :-)Thanks,Daniel

Filetable on separate device

Posted: 21 Aug 2013 06:22 AM PDT

Hello,I've created a VM (using Windows Virtual PC) running Windows 7, 32-bit, and allocated a virtual hard drive of 126 MB.The virtual hard drive is quite limited, and that's the way I want it.On the VM, I have setup SQL Server 2012, 32-bit.I am about to conduct an experiment using SQL Server 2012 Filetables.Is it possible to setup a Filetable where the data for it is on a separate device (NAS) with a separate IP address?The VM can connect to the NAS through it's fixed IP address. I can not only ping it from the VM, but I can also map a network drive to it.However, I cannot create the Filestream device for the database on it.This works:Create Database RRTestOn Primary ( Name = RRTestData , Filename = 'C:\Data\MSSQL\RRTest.mdf' , Size = 10MB , FileGrowth = 1MB ), FileGroup FSPics Contains FileStream Default ( Name = FSPics , Filename = 'C:\Data\MSSQL\FSPics\' )Log On ( Name = RRTestLog , Filename = 'C:\Data\MSSQL\RRTest.ldf' , Size = 1MB , FileGrowth = 1MB )But this doesn't:Create Database RRTestOn Primary ( Name = RRTestData , Filename = 'C:\Data\MSSQL\RRTest.mdf' , Size = 10MB , FileGrowth = 1MB ), FileGroup FSPics Contains FileStream Default ( Name = FSPics , Filename = '\\192.168.0.32\Volume_1\Data\MSSQL\FSPics\' )Log On ( Name = RRTestLog , Filename = 'C:\Data\MSSQL\RRTest.ldf' , Size = 1MB , FileGrowth = 1MB )I have followed these instructions written in BOL:' filestream_path 'For a FILESTREAM filegroup, FILENAME refers to a path where FILESTREAM data will be stored. The path up to the last folder must exist, and the last folder must not exist. For example, if you specify the path C:\MyFiles\MyFilestreamData, C:\MyFiles must exist before you run ALTER DATABASE, but the MyFilestreamData folder must not exist.The filegroup and file (<filespec>) must be created in the same statement.The SIZE and FILEGROWTH properties do not apply to a FILESTREAM filegroup.I have already created a folder on the NAS, "Data\MSSQL\", but not "Data\MSSQL\FSPics\Can this be done at all?Thanks for any suggestions and pointers.Richard

DQS NULL REFERENCE ERROR

Posted: 17 Jul 2013 03:06 AM PDT

Experiencing a DQS error whilst running an SSIS package with a DQS component. I have created a DQS knowledge which is accessed by the DQS component. When the package is run from SQL Server agent we get the following error:[size="1"]DFT_ProcessWorkData:Error: Microsoft.Ssdqs.Infra.Exceptions.EntryPointException: The Knowledge Base does not exist [Id : 1030017]. at Microsoft.Ssdqs.Proxy.Database.DBAccessClient.Exec() at Microsoft.Ssdqs.Proxy.EntryPoint.KnowledgebaseManagementEntryPointClient.DQProjectGetById(Int64 id) at Microsoft.Ssdqs.Component.DataCorrection.Logic.DataCorrectionComponent.PostExecute() at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPostExecute(IDTSManagedComponentWrapper100 wrapper)DFT_ProcessWorkData:Error: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.Ssdqs.Component.DataCorrection.Logic.DataCorrectionComponent.ProcessChunk(ReadOnlyCollection`1 fieldMappings, ReadOnlyCollection`1 records, CorrectedRecordsStatusStatistics& correctedRecordsTotalStatusStatistics) at Microsoft.Ssdqs.Component.DataCorrection.Logic.DataCorrectionComponent.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)DFT_ProcessWorkData:Error: An unexpected error occurred in the DQS infrastructure.System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.Ssdqs.Component.DataCorrection.Logic.DataCorrectionComponent.ProcessChunk(ReadOnlyCollection`1 fieldMappings, ReadOnlyCollection`1 records, CorrectedRecordsStatusStatistics& correctedRecordsTotalStatusStatistics)[/size]When the packages were run on our development server they ran fine. The same packages were then deployed to our RTE environment. DQS was also installed on our RTE environment. SQL Jobs were then scheduled to run the packages. The jobs failed giving the above error. I ran the package from visual studio and the package ran successfully. This led me to believe it was a rights issue. I therefore changed the credential and proxy account which ran the sql jobs to ensure that they had the necessary rights. I tried executing the package again from Sql server agent but still got the same error.We uninstalled DQS on our RTE environment and reinstalled it again but the problem still remains. Anybody else experienced this issue or have any idea what may be causing the error.

SSMS connection to SSIS with packages on remote MSDB server - is that even remotely possible?

Posted: 01 Mar 2013 09:03 AM PST

I've been working in a lab to see what is possible as far as separation of services in SQL Server 2012.I have this setup:Server 1: DB EngineServer 2: SSIS & SSASMy goal is to have the SSIS packages in a database on the main DB server (Server 1) and have the users access Server 2's SSIS via SSMS from their workstation. I'm finding that this is perhaps not possible. [url=http://www.sqlservercentral.com/Forums/Topic1373364-1550-1.aspx#bm1373371]As this forum post states:[/url]Which quotes [url=http://msdn.microsoft.com/en-us/library/aa337083(v=sql.110).aspx]this article from MS[/url]:[quote]Delegation Is Not SupportedSQL Server Integration Services does not support the delegation of credentials, sometimes referred to as a double hop. In this scenario, you are working on a client computer, Integration Services is installed on a second computer, and SQL Server is installed on a third computer. Although SQL Server Management Studio successfully passes your credentials from the client computer to the second computer on which Integration Services is running, Integration Services cannot delegate your credentials from the second computer to the third computer on which SQL Server is running.[/quote]So it looks like i finally hit a brick wall. I'm wondering if there is any way to configure such a scenario (mostly for knowledge) or if that is even practical. I'd be curious to hear from experienced DBA's who can perhaps point me in the right direction. Thank you in advance.

Why queries are using certain indexes

Posted: 21 Aug 2013 05:05 AM PDT

I have the following query written two ways. The first query uses different indexes than the second and I am trying to understand why. Why wouldn't the optimizer figure out to use the same indexes for an or clause versus a union all. [code="sql"][b]QUERY #1[/b]SELECT TOP 400 *FROM AuditLog WITH ( NOLOCK )WHERE CustomerID = 2379 AND PracticeID = 55 AND ( ( AuditTypeID = 4 AND EntityTypeID = 1022638 ) OR ( AuditTypeID = 6 AND SubEntityTypeID = 1022638 ) )ORDER BY CreatedDate DESC [b]QUERY #2[/b]SELECT TOP 400 *FROM (SELECT TOP 400 *FROM AuditLog WITH ( NOLOCK )WHERE CustomerID = 2379 AND PracticeID = 55 AND ( AuditTypeID = 4 AND EntityTypeID = 1022638 )ORDER BY CreatedDateUNION ALLSELECT TOP 400 *FROM AuditLog WITH ( NOLOCK )WHERE CustomerID = 2379 AND PracticeID = 55 AND ( AuditTypeID = 6 AND SubEntityTypeID = 1022638 )ORDER BY CreatedDate)subORDER BY sub.CreatedDate[/code][code="xml"]<?xml version="1.0" encoding="utf-16"?><ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.3373.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> <BatchSequence> <Batch> <Statements> <StmtSimple StatementCompId="2" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.00657216" StatementText="SELECT TOP 400 * FROM AuditLog WITH ( NOLOCK ) WHERE CustomerID = 2379 AND PracticeID = 55 AND ( ( AuditTypeID = 4 AND EntityTypeID = 1022638 ) OR ( AuditTypeID = 6 AND SubEntityTypeID = 1022638 ) ) ORDER BY CreatedDate DESC " StatementType="SELECT" QueryHash="0x9F3AF4664382C044" QueryPlanHash="0x2CA78D552FCE421D" RetrievedFromCache="true"> <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /> <QueryPlan DegreeOfParallelism="1" CachedPlanSize="40" CompileTime="5" CompileCPU="5" CompileMemory="368"> <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" /> <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="341333" EstimatedPagesCached="512000" EstimatedAvailableDegreeOfParallelism="6" /> <RelOp AvgRowSize="4238" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.00657216"> <OutputList> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="6" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <Top RowCount="false" IsPercent="false" WithTies="false"> <TopExpression> <ScalarOperator ScalarString="(400)"> <Const ConstValue="(400)" /> </ScalarOperator> </TopExpression> <RelOp AvgRowSize="4238" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038"> <OutputList> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="6" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </OuterReferences> <RelOp AvgRowSize="31" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Index Seek" NodeId="2" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="175160000"> <OutputList> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="62088" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <IndexScan Ordered="true" ScanDirection="BACKWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> <DefinedValues> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </DefinedValue> </DefinedValues> <Object Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Index="[IX_AuditLog_CustomerID_PracticeID_CreatedDate]" IndexKind="NonClustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="(2379)"> <Const ConstValue="(2379)" /> </ScalarOperator> <ScalarOperator ScalarString="(55)"> <Const ConstValue="(55)" /> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> <RelOp AvgRowSize="4214" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="175160000"> <OutputList> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="6" ActualEndOfScans="62082" ActualExecutions="62088" /> </RunTimeInformation> <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> <DefinedValues> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> </DefinedValue> </DefinedValues> <Object Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Index="[PK_AuditLog]" TableReferenceId="-1" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[KareoAudit].[dbo].[AuditLog].[AuditLogID]"> <Identifier> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> <Predicate> <ScalarOperator ScalarString="[KareoAudit].[dbo].[AuditLog].[AuditTypeID]=(4) AND [KareoAudit].[dbo].[AuditLog].[EntityTypeId]=(1022638) OR [KareoAudit].[dbo].[AuditLog].[AuditTypeID]=(6) AND [KareoAudit].[dbo].[AuditLog].[SubEntityTypeId]=(1022638)"> <Logical Operation="OR"> <ScalarOperator> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(4)" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(1022638)" /> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> <ScalarOperator> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(6)" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(1022638)" /> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </Logical> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </NestedLoops> </RelOp> </Top> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> <Batch> <Statements> <StmtSimple StatementCompId="3" StatementEstRows="2" StatementId="2" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0245098" StatementText="SELECT TOP 400 * FROM ( SELECT TOP 400 * FROM AuditLog WITH ( NOLOCK ) WHERE CustomerID = 2379 AND PracticeID = 55 AND ( AuditTypeID = 4 AND EntityTypeID = 1022638 ) ORDER BY CreatedDate UNION ALL SELECT TOP 400 * FROM AuditLog WITH ( NOLOCK ) WHERE CustomerID = 2379 AND PracticeID = 55 AND ( AuditTypeID = 6 AND SubEntityTypeID = 1022638 ) ORDER BY CreatedDate)sub ORDER BY sub.CreatedDate " StatementType="SELECT" QueryHash="0x85D7E4BBA213B22A" QueryPlanHash="0x9FBEFBBE742E66A3" RetrievedFromCache="true"> <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /> <QueryPlan DegreeOfParallelism="1" MemoryGrant="1024" CachedPlanSize="64" CompileTime="7" CompileCPU="7" CompileMemory="544"> <MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="560" RequiredMemory="512" DesiredMemory="560" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="16" /> <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="341333" EstimatedPagesCached="512000" EstimatedAvailableDegreeOfParallelism="6" /> <RelOp AvgRowSize="4238" EstimateCPU="0.000107358" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2" LogicalOp="TopN Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0245098"> <OutputList> <ColumnReference Column="Union1006" /> <ColumnReference Column="Union1007" /> <ColumnReference Column="Union1008" /> <ColumnReference Column="Union1009" /> <ColumnReference Column="Union1010" /> <ColumnReference Column="Union1011" /> <ColumnReference Column="Union1012" /> <ColumnReference Column="Union1013" /> <ColumnReference Column="Union1014" /> <ColumnReference Column="Union1015" /> <ColumnReference Column="Union1016" /> <ColumnReference Column="Union1017" /> <ColumnReference Column="Union1018" /> <ColumnReference Column="Union1019" /> </OutputList> <MemoryFractions Input="1" Output="1" /> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="6" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <TopSort Distinct="false" Rows="400"> <OrderBy> <OrderByColumn Ascending="true"> <ColumnReference Column="Union1019" /> </OrderByColumn> </OrderBy> <RelOp AvgRowSize="4238" EstimateCPU="2E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2" LogicalOp="Concatenation" NodeId="1" Parallel="false" PhysicalOp="Concatenation" EstimatedTotalSubtreeCost="0.0131412"> <OutputList> <ColumnReference Column="Union1006" /> <ColumnReference Column="Union1007" /> <ColumnReference Column="Union1008" /> <ColumnReference Column="Union1009" /> <ColumnReference Column="Union1010" /> <ColumnReference Column="Union1011" /> <ColumnReference Column="Union1012" /> <ColumnReference Column="Union1013" /> <ColumnReference Column="Union1014" /> <ColumnReference Column="Union1015" /> <ColumnReference Column="Union1016" /> <ColumnReference Column="Union1017" /> <ColumnReference Column="Union1018" /> <ColumnReference Column="Union1019" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="6" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <Concat> <DefinedValues> <DefinedValue> <ColumnReference Column="Union1006" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1007" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1008" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1009" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1010" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1011" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1012" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1013" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1014" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1015" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1016" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1017" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1018" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1019" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="4238" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Top" NodeId="2" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.00657048"> <OutputList> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <Top RowCount="false" IsPercent="false" WithTies="false"> <TopExpression> <ScalarOperator ScalarString="(400)"> <Const ConstValue="(400)" /> </ScalarOperator> </TopExpression> <RelOp AvgRowSize="4238" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038"> <OutputList> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </OuterReferences> <RelOp AvgRowSize="39" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Index Seek" NodeId="4" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="175160000"> <OutputList> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> <DefinedValues> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </DefinedValue> </DefinedValues> <Object Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Index="[IX_AuditLog_EntityTypeID_CustomerID_PracticeID_AuditTypeID]" TableReferenceId="1" IndexKind="NonClustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="(1022638)"> <Const ConstValue="(1022638)" /> </ScalarOperator> <ScalarOperator ScalarString="(2379)"> <Const ConstValue="(2379)" /> </ScalarOperator> <ScalarOperator ScalarString="(55)"> <Const ConstValue="(55)" /> </ScalarOperator> <ScalarOperator ScalarString="(4)"> <Const ConstValue="(4)" /> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> <RelOp AvgRowSize="4205" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="6" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="175160000"> <OutputList> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="0" ActualExecutions="5" /> </RunTimeInformation> <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> <DefinedValues> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> </DefinedValue> </DefinedValues> <Object Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Index="[PK_AuditLog]" TableReferenceId="-1" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[KareoAudit].[dbo].[AuditLog].[AuditLogID]"> <Identifier> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </NestedLoops> </RelOp> </Top> </RelOp> <RelOp AvgRowSize="4238" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Top" NodeId="25" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.00657048"> <OutputList> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <Top RowCount="false" IsPercent="false" WithTies="false"> <TopExpression> <ScalarOperator ScalarString="(400)"> <Const ConstValue="(400)" /> </ScalarOperator> </TopExpression> <RelOp AvgRowSize="4238" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="26" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038"> <OutputList> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </OuterReferences> <RelOp AvgRowSize="39" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Index Seek" NodeId="27" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="175160000"> <OutputList> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> <DefinedValues> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </DefinedValue> </DefinedValues> <Object Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Index="[IX_AuditLog_SubEntityTypeID_CustomerID_PracticeID_AuditTypeID]" TableReferenceId="2" IndexKind="NonClustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="(1022638)"> <Const ConstValue="(1022638)" /> </ScalarOperator> <ScalarOperator ScalarString="(2379)"> <Const ConstValue="(2379)" /> </ScalarOperator> <ScalarOperator ScalarString="(55)"> <Const ConstValue="(55)" /> </ScalarOperator> <ScalarOperator ScalarString="(6)"> <Const ConstValue="(6)" /> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> <RelOp AvgRowSize="4205" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="29" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="175160000"> <OutputList> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" /> </RunTimeInformation> <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> <DefinedValues> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> </DefinedValue> </DefinedValues> <Object Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Index="[PK_AuditLog]" TableReferenceId="-1" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[KareoAudit].[dbo].[AuditLog].[AuditLogID]"> <Identifier> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </NestedLoops> </RelOp> </Top> </RelOp> </Concat> </RelOp> </TopSort> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> </BatchSequence></ShowPlanXML>[/code][code="xml"][/code]

Help with IIS and configuring the connection string for a .net app

Posted: 21 Aug 2013 05:11 AM PDT

I am a sql server DBA. I am not completely aware of how IIS recognizes a sql server windows login credentials.This is for our testing only and doesnt reflect any prod or development env's. Right now we have created a new domain TESTApp.xyz.local and added an Active Passive windows and SQL Server Cluster set up to this domain. So my test database server is testdb\apptest. Our test app server is sitting on a different domain which is abc.local. The app server name is appserver1. Now I have restored the databases in testdb\apptest instance for the apps being hosted on appserver1. How do I ensure that the user will be able to login to application?Apps are using .net framework 4 and using IIS. If i create a domain account say appuser.xyz.local and create a login for this in testdb\apptest and give appropriate permissions on the database then how will the app be able to use this user to login to the app? what changes are required here at IIS as well as config file level. Currently the config files are using integrated security.Need your help experts
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog