Thursday, May 9, 2013

[SQL Server] How to debug query

[SQL Server] How to debug query

How to debug query

Posted: 09 May 2013 03:00 AM PDT

I have a query that used to work fine (last week), but now runs forever. It is a massive query with many sub-queries, but it was running in a few seconds. I know I could run each subquery one at a time to identify the bad one, but is there a way to use SSMS to tell me where the query is running so long? I popped up the execution plan. It's large and difficult to understand for me. It does show me a line as follows. Is it possible this is my problem? Is there a way to do further investigation?I would post the query, but it's 50 lines long, using 20+ tables and views. It basically summarizes the whole system into a flat row for reporting purposes.[code="sql"]/*Missing Index Details from SQLQuery9.sql - MTXXXXXXXX(xxxx)The Query Processor estimates that implementing the following index could improve the query cost by 17.2663%.*//*USE [CPAS]GOCREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]ON [dbo].[AllLaborInput] ([VENDOR NAME])INCLUDE ([SITE],[PROJ #],[START DATE],[WORKER NO],[REG HRS],[OT HRS],[LINE COST])GO*/[/code]

joins

Posted: 09 May 2013 02:32 AM PDT

Ok so i am diving back into SQL now and I am stuck. I am using views to manipulate my data and get it to where i want it. But now I need to make it into a table... the problem is the column names are all different than the tables I have used previously and i want to make sure that all the data (from 2002 till now) are compatible and have the same format. So i need a way to append to these new tables to the old ones. I have tried a few things but nothing seems to work. Any help will be appreciated.

SSRS drill-through report - passing only 2 of 3 parameter values

Posted: 09 May 2013 08:31 AM PDT

SSRS is not passing the last parameter in my drill through report...the first 2 work fine, but the 3rd requires me to select from available values defined in the Child report. Omit is set to False on all 3 parameters and there are no Default values in the Child report. Any suggestions?Below is a portion of the .rdl file for reference: <Style> <TextAlign>Center</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>BusinessSection1</rd:DefaultName> <ActionInfo> <Actions> <Action> <Drillthrough> <ReportName>DetailBySection</ReportName> <Parameters> <Parameter Name="Method"> <Value>=Fields!Method.Value</Value> </Parameter> <Parameter Name="Section"> <Value>=Fields!BusinessSection.Value</Value> </Parameter> <Parameter Name="DateRangeType"> <Value>=Fields!DateRangeType.Value</Value> ***this value is not being passed*** </Parameter> </Parameters> </Drillthrough> </Action> </Actions> </ActionInfo> <ToolTip>Click to View Detailed Report</ToolTip>

How to raise alert based on rowcount in temp table

Posted: 09 May 2013 01:27 AM PDT

Hello,I have some code that creates a temporary table based on output from sp_who. I query the temp table to see if anyone is logged in as a certain generic userID from their own host. If they have, it's a misuse of the generic ID and a security breach. That part works fine.Here's the part I need help with:If the rowcount is not zero, I want to raise an alert and send an email. Could someone please help me with how I would check for this (code sample) or point me to a resource? I found a similar-sounding question but their intent was different enough that I still don't know how to do it.I believe that I know how to set up a job and send an email. It's the code for checking for zero records I need an example for. Thanks.

Maintenance cleanup task 0k button grayed out

Posted: 29 Feb 2012 01:28 AM PST

Recently added a new maintenance cleanup task to an instance (SQL Server 2008). While the task worked I decided that I wanted to reduce the number of files retained. While attempting to change the plan - I noticed the ok button was grayed out. After making the changes to the plan, the ok button was still grayed out, and my changes could not be saved.Note that this was a standalone, it was not coupled with a backup task. I have not attempted to delete the cleanup task. Are there any suggestions or know issues with this?Any comments / urls are appreciated - thank you.

JOIN on tables dropping records

Posted: 09 May 2013 02:06 AM PDT

Hi,I have Table A:with 419430 records, Table B with 19372 Records.I am doing join on these 2 tables in order to calculate 2 new columnsBDGT_UTIL_AMT and BDGT_MBRSHIP_MO_CNT.When I am joining the 2 tables records are drop, I am expecting 419430 records as result instead I am getting result of 214868 records, 204,562 records are dropped.I have attach the data of Table 1:dev1_metadata_etl.DLDR_BDGT_UTIL_O and Table 2:PROD_DEPT_DLDR_TBL.DLDR_BDGT_MBRSHIP_O and the data I getting as Result of 214868 records.I have them in text file.select DLDR_RDM.FILE_CTRL_ID,DLDR_RDM.ROW_NUM, DLDR_RDM.BDGT_SCEN_CD, DLDR_RDM.HLTH_PLAN_BDGT_CD, DLDR_RDM.PDCT_BDGT_CD,DLDR_RDM.GL_ACCT_CD, DLDR_RDM.EXPRNC_COHORT_CD, DLDR_RDM.BDGT_UTIL_MEAS_CD, DLDR_RDM.BDGT_YR_MO_NUM,DLDR_RDM.SRC_METRIC_VALUE,CAST(((DLDR_RDM.BDGT_AMT/12000) * DLDR_MEM.BDGT_AMT) AS DECIMAL(18,8)) AS BDGT_UTIL_AMT,DLDR_MEM.BDGT_AMT AS BDGT_MBRSHIP_MO_CNT from dev1_metadata_etl.DLDR_BDGT_UTIL_O DLDR_RDMInner join PROD_DEPT_DLDR_TBL.DLDR_BDGT_MBRSHIP_O DLDR_MEMon DLDR_RDM.BDGT_SCEN_CD=DLDR_MEM.BDGT_SCEN_CDAND DLDR_RDM.HLTH_PLAN_BDGT_CD=DLDR_MEM.HLTH_PLAN_BDGT_CDAND DLDR_RDM.PDCT_BDGT_CD=DLDR_MEM.PDCT_BDGT_CDAND DLDR_RDM.BDGT_YR_MO_NUM=DLDR_MEM.BDGT_YR_MO_NUMAND DLDR_RDM.EXPRNC_COHORT_CD=DLDR_MEM.EXPRNC_COHORT_CDWHERE DLDR_MEM.FILE_CTRL_ID = 1502Columns of Table 1:FILE_CTRL_ID ROW_NUM BDGT_SCEN_CD HLTH_PLAN_BDGT_CD PDCT_BDGT_CD GL_ACCT_CD EXPRNC_COHORT_CD BDGT_UTIL_MEAS_CD BDGT_YR_MO_NUM SRC_METRIC_VALUE BDGT_AMTTable 2:FILE_CTRL_ID ROW_NUM BDGT_SCEN_CD HLTH_PLAN_BDGT_CD PDCT_BDGT_CD GL_ACCT_CD EXPRNC_COHORT_CD BDGT_YR_MO_NUM BDGT_AMTMy result Columns:FILE_CTRL_ID ROW_NUM BDGT_SCEN_CD HLTH_PLAN_BDGT_CD PDCT_BDGT_CD GL_ACCT_CD EXPRNC_COHORT_CD BDGT_UTIL_MEAS_CD BDGT_YR_MO_NUM SRC_METRIC_VALUE BDGT_AMT,BDGT_UTIL_AMT ,BDGT_MBRSHIP_MO_CNTI did full outer join and I am getting 215096 recordsLeft outer join: 214868Inner join : 214868Please helpThanks

Migrating from Access to SQL 2008

Posted: 08 May 2013 10:52 PM PDT

Can someone explain the 3 different options in the upsizing wizard?1) Create a new Access client/server application2) Link SQL server tables to existing application3) No Application changesI want a brand new SQL database without any attachment to Access, which one do I choose?Should I use something other than the upsizing wizard?

must declare the scalar variable!

Posted: 08 May 2013 06:45 PM PDT

hello every body.I have a question about declaring variable in sql server 2012.when i declare a variable for example :[code="other"] DECLARE @X INT; [/code]when i compile this line it's truebut when i set a value in different line to this variable: [code="other"] SET @X =12; [/code]it returns an error message as:[code="other"] Must declare the scalar variable @X [/code]how can i fix it?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog