Wednesday, May 1, 2013

[SQL Server 2008 issues] Disk_Queue_Length is CRITICAL or high

[SQL Server 2008 issues] Disk_Queue_Length is CRITICAL or high

Disk_Queue_Length is CRITICAL or high

Posted: 25 Apr 2013 04:58 AM PDT

This is regarding the Cluster servers 723/722/721 3 Nodes (Active/Active/Passive). At around Fri Mar 29 11:05:42 EDT 2013, We have got alert from monitoring agent Nagios that "Disk_Queue_Length is CRITICAL" and during that time SQL stopped responding for a couple minutes and this happened about 3 times.Since that time I disabled the Nagios alert on that particular event.Storage Team/Backup Team/Wintel Team - saying that no issues found from their end.Windows Administration team says its everything normal on their side. SQL Team - Saying that when this issue happened that time there was no jobs were running on this cluster and no issues. Can any one suggest here where exactly issue is and how to resolve this issue.Thanks a million in advance.

Are my statistics being used?

Posted: 30 Apr 2013 07:07 AM PDT

Hi,I have a system with a lot of blocking. From the last occurrence I found a statistics update causing a lot of the blocking:[code="sql"]SELECT StatMan([SC0], [SC1], [SC2], [SC3], [SC4], [SC5], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], [SC1], [SC2], [SC3], [SC4], [SC5], step_direction([SC0]) over (order by NULL) AS [SB0000] ...[/code]I went to the table in question and found a lot of statistics with the dta prefix meaning that someone had let DTA auto create a lot of statistics at some point.I want to verify that these are actually being used. If I have a bunch of unused statistics which are resulting in blocking whenever auto stats update runs, I'd like to drop the stats.Unfortunately I don't know how to check if my statistics are being used. Any ideas?

count(*) vs count(1) or count(id)

Posted: 30 Apr 2013 05:01 PM PDT

Dear Experts,I want to know what is difference between count(*), count(id) and count(1) base on performance issues.We have some quires with count(*) that sometimes throws timeout and we have many Transactions on these tables in our other queries.we guess that if we change it to count(id) or count(1) it will be better.Is this change effective? Any information will be helpful.Faithfully Yours,Ashkan

why there are no sql server services listed in configuration manager

Posted: 30 Apr 2013 10:00 AM PDT

In installed another named instance of same edition and version sql server 2008 r2 on a server.Now i can t see any services related sqlserver.and also the instance status button is white in color. I know it shows green when the server is running and red when the server is stopped..Any inputs please ?

display specific string and that positions in sql server

Posted: 30 Apr 2013 02:32 PM PDT

hi friend i have small doubt in sql server plz tell me how to solve thisi want display only specific string in based on table data suppose to disply 'm' string based on table and that positionnamesas programermms programermy name is ravikumariam sas programeriam good in sasbased on this i want output like this name , string , positionsas programer , m , 11mms programer , m , 1mms programer , m , 2mms programer , m , 11my name is ravikumar , m , 1my name is ravikumar , m , 6my name is ravikumar , m , 18iam sas programer , m , 3iam sas programer , m , 15iam good in sas , m , 3plz tell me how to wirte query in sql server to solve this issue.

Trouble with SQL Server Agent 2008

Posted: 30 Apr 2013 01:45 AM PDT

Hello all. This is on a Windows 7 Pro machine. As I mentioned in an earlier post today, I am being thrown back into the IT world after being gone for many years. Much catching up to do on my part!! Anyways....I am trying to install and run on SQL Server 2008 Express and SQL Server 2008 Management Studio. I recently installed SQL Server 2008 Express (today) and we already had SQL Server Management Studio installed for some time now on the same machine. Management Studio is being used to connect to a remove SQL Server and is working fine.As I understand it, SQL Server Express installs and uses a couple of services called SQL Server Agent and SQL Server Browser. For some reason, SQL Server Agent cannot be started. Here's a screenshot of the SQL Server Configuration Manager:[img]http://www.justdrumsonline.com/Assets/images/image1.png[/img]When I look in Windows Event Viewer, I see this error recorded:[img]http://www.justdrumsonline.com/Assets/images/image2.png[/img]Finally, I've noticed that when I use and run SQL Server Management Studio 2008, SQL Server 2008 Express is not listed as a Server Type....[img]http://www.justdrumsonline.com/Assets/images/image3.png[/img]....nor is my local copy of SQL Server 2008 Express shown. The newly installed instance is called "SQLEXPRESSJD"...which if I'm not mistaken, should be showing up in this list.[img]http://www.justdrumsonline.com/Assets/images/image4.png[/img]One thing that does concern me is that in the picture above, the one that shows the Server Types, it shows SQL Server Compact Edition, not Express. I never installed "Compact" so I'm not sure why this is shown. I'm hesitant to remove it or uninstall it from the Control Panel because another vital programs like Quick Books or Point of Sale (Intuit programs) might need it.I would appreciate any help anyone can offer! I'll send $5 via PayPal to anyone who can get this working! ;-)

Ports and Firewall changes for named Instance

Posted: 30 Apr 2013 09:34 AM PDT

Hi All,What all ports should be opened for a named instance, which is listeneing on 1477.The named instance name is PDCZV\CZV01When security opened port number 1477, the developers are able to connect to this named instance using PDCZV,1477 but not PDCZV\CZV01Can someone please let me know this.Thank you

2008 Central Management Server - Schedule Multi Server Query?

Posted: 08 Oct 2010 12:38 AM PDT

Hi Everyone - I have a 2008 Central Management Server that we're able to run queries from against all our SQL servers. I'm wondering if there's a way to do this via the command line or a job so the queries can be automated and rolled up into reporting services reports. Any suggestions?

How many packet errors are acceptable before I worry?

Posted: 30 Apr 2013 12:17 PM PDT

We collect network information and report on it daily. We show packets sent, received, and errors. Typically there are no errors and sent and received in the thousands. Yesterday I saw 3-5 packet errors total. How do I know when to consider this an issue with networking?

MCM Free Videos - Demo Scripts from Paul/Kim/Brent/Ben

Posted: 17 May 2012 10:04 PM PDT

http://www.sqlskills.com/T_MCMVideos.aspI read a blog post from 01/2011 from Paul saying the scripts would be released through blog articles."[url=http://www.sqlskills.com/BLOGS/PAUL/post/All-MCM-free-training-videos-now-available.aspx]PS Some people have asked for the demo scripts from the videos - these will be blogged by us through the year[/url]."[b]Is there one file or a set of files I can download from sqlskills.com that have all the demo scripts used in the videos?[/b]I'm currently reading on the Resource Governor and creating custom resource pools. I didn't find a blog post on sqlskills that show the scripts Paul used. Are these scripts available? Googling site specific to sqkskills didn't reveal anything. Widening my search didn't either. I'm getting up there in age, so if any of you youngsters can help and old man, I'd appreciate it. ;-)

Save all DTS pakges to location

Posted: 30 Apr 2013 08:51 AM PDT

Hi I found this script for saving all DTS pkges.Can someone help me in sacving all the DTS t0 H:\DTS location--------------------------------------------------------------if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_SavePackages]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[s_SavePackages]GOCreate procedure s_SavePackages@Path varchar(128)as/**/ set nocount ondeclare @objPackage intdeclare @PackageName varchar(128)declare @rc intdeclare @ServerName varchar(128)declare @FileName varchar(128)declare @FilePath varchar(128)declare @cmd varchar(2000) select @ServerName = @@ServerName , @FilePath = @Path if right(@Path,1) <> '\' begin select @Path = @Path + '\' end -- create output directory - will fail if already exists but ... select @cmd = 'mkdir ' + @FilePath exec master..xp_cmdshell @cmd create table #packages (PackageName varchar(128)) insert #packages (PackageName) select distinct name from msdb..sysdtspackages select @PackageName = '' while @PackageName < (select max(PackageName) from #packages) begin select @PackageName = min(PackageName) from #packages where PackageName > @PackageName select @FileName = @FilePath + @PackageName + '.dts' exec @rc = sp_OACreate 'DTS.Package', @objPackage output if @rc <> 0 begin raiserror('failed to create package rc = %d', 16, -1, @rc) return end exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null, @ServerName = @ServerName, @Flags = 256, @PackageName = @PackageName if @rc <> 0 begin raiserror('failed to load package rc = %d, package = %s', 16, -1, @rc, @PackageName) return end -- delete old file select @cmd = 'del ' + @FileName exec master..xp_cmdshell @cmd, no_output exec @rc = sp_OAMethod @objPackage, 'SaveToStorageFile', null, @FileName if @rc <> 0 begin raiserror('failed to save package rc = %d, package = %s', 16, -1, @rc, @PackageName) return end exec @rc = sp_OADestroy @objPackage endgo

Odd Error Message

Posted: 07 Mar 2012 07:32 PM PST

MorningJust going throught my 2012 SSMS to a 2008r2 server, and I got the following error message, which I've never seen before.[b]Cannot display policy health state at the server level, because the user doesn't have permission. Permission to access the msdb database is required for this feature to work correctly.[/b]which is a bit odd as I'm the sysadmin on the box. Once I've clicked through I seem to be able to do all I need to do on the box.I can find nothing online, and when clicking the link for more help, it comes back with no information in the Knowledge base at this time.Anyone got any ideas?Tapete

Table Partitioning Scenario question.

Posted: 30 Apr 2013 06:05 AM PDT

Hi Experts,Migrating warehouse from Oracle to SQL Server : Status: In processIn Oracle : 6 SchemasIn SQL Server: Created a database with 6 Schemas.-Every Schema has 3 Paritioned and 3 non partitioned tables.-Out of 3 partitioned tables one is parent table and all other 5 tables are child tables.-Only parent table has date column and non other table has date column for partitioning.-all the 5 tables in each schema has primary key.My Question is can I partition parent table with date column and child tables with Primary key column ?If so please can someone advise the partitioning method for a parent table and child tables.sometimes the partitioning column in parent table has abnormal dates like 2099,2088. How can i seperate those values. I listened that we can do by MAX and MIN dates while partitioning but not sure how to implement that..Thanks for all your time.

Assigning a fieldname with rollup

Posted: 16 Apr 2013 04:26 AM PDT

I have two tables that have racing data, and I need to add up the number of cancelled races and make it my aliased field name (in this case as you see the query below numCancelled, meaning number of races cancelled):(select a.eid, count(*) as numCancelled from(select ev4.eid,f.nvid, f.perfdate, f.racenumber from Finish f (nolock)join event ev4 (nolock) on ev4.eid = f.eid wheref.NoRace = 'true' and f.PerfDate = '2013-04-11') a group by a.eid with rollup)....the resultset here just lists all the eids (event ids) then the rollup total (in this case it is referred to a NULL in that eid column), but I need my numCancelled field to contain that grand total 7, as right now it comes back as 1 when I run this subquery in my stored procedure.??Z

SQL query to XML ???

Posted: 30 Apr 2013 12:43 AM PDT

Hi,I',m new to XML and here is what I need to do...I want to create a SQL query to pull data and update a field on a table with current date and another field an "X"I need the data in the select to be written to an XML.I then need the XML file emailed,Once emailed I need to run another query to update the 'X" to 'Y' showing its been sent...I assume I can do this from some job steps?I really don't know where to start??ThanksJoe

Does linking tables to Access change the size of SQL Server database?

Posted: 30 Apr 2013 05:09 AM PDT

I noticed that the size of db copy with linked tables is 6 times higher than the original one. Copy has the same tables with the same records as the production db. Is this because of Access linking? Size (Mb) Space Available (Mb)Production Db 341 197Copy with linked table 1952 5ThanksVal

Unable to restore the database DatabaseName=*** INCOMPLETE ***

Posted: 30 Apr 2013 02:27 AM PDT

Dear expert,I did lots of googlings and most suggestions pointing to a corrupted database, but I still have some doubt about if the database is really being corrupted. A client created a backup of their database in SQL server 2005 and shipped to us from another state. When I tried to restore on SQL server 2008 R2 (I also tried on SQL server 2005 as well). Restoration failed. When I run the following query against the backup file, it returned databasename as "*** INCOMPLETE ***" and the rest of the fields are NULLs, except Position has a value of 1.[code="sql"]RESTORE HEADERONLY FROM DISK='C:\Data\Database\SQLBackup\ClientDBName.bak'GO[/code]When I ran the following query,[code="sql"]RESTORE VERIFYONLY FROM DISK = 'C:\Data\Database\SQLBackup\ClientDBName.bak'GO[/code]I got this[code="plain"]Msg 3242, Level 16, State 2, Line 2The file on device 'C:\Data\Database\SQLBackup\ClientDBName.bak' is not a valid Microsoft Tape Format backup set.Msg 3013, Level 16, State 1, Line 2VERIFY DATABASE is terminating abnormally.[/code] I asked client resending me the backup copy with CHECKSUM option turned on but still no luck. I tried to restore with CONTINUE_AFTER_ERROR and still unsuccessful.Thanks in advance for any clue.

RSClintPrint

Posted: 07 Aug 2012 07:05 AM PDT

So we are in the process of converting over from Crystal to SSRS. Things seem to be going fine; however, we realize an issue today. Whenever the users try to print the first time, it tells them that you need to install SQL Server Reporting Services, which when you do, allows you to print just fine. The problem is, it's a pain because we have to log in as administrator, install this, log out and then let the user attempt to print the report again. I want to be able to just push this out to the whole network, as we continue to grow our SSRS, all users will need to print reports. I've done some research, but haven't really found a good solution to pushing this out. Please help, as I have converted a few departments over to the new reports, prior to realizing this was an issue.Thanks,Jordon

Checking for index in another database

Posted: 30 Apr 2013 01:28 AM PDT

Folks:How can i check for the presence of an index in another database ?Lets say I am in DATABASE A and i want to check for an object in database B. The following syntax does not work. I tried it.To make it even easier how can I check for the sys.indexes view in another database ?USE DATAEXCH;GOIF EXISTS ( SELECT name FROM sys.indexes WHERE name = N'inx_patient_encounter1' AND object_id = OBJECT_ID(N'NGPROD.dbo.patient_encounter')) Begin Print 'Index Found'EndelseBegin Print 'Index Not Found'End

copying jobs history

Posted: 30 Apr 2013 02:45 AM PDT

Is it ok to copy sysjobhistory table from one server to another or is there any pitfalls

Blocking

Posted: 30 Apr 2013 01:21 AM PDT

Hi,We are running one job which runs SELECT query from a view from another linked server. We are getting blocking in the linked server where SELECT query from View is causing the same. The view is written with NOLOCK. So I am confused, how SELECT query can be culprit blocker when using NOLOCK option?Please help.

display specific string and display that position in sql server

Posted: 30 Apr 2013 01:47 AM PDT

hi firends i have small doubt in sql server plz tell me how to solve to find the specific charter and that posittion. here i want find sas string .which position its there and diplay with sas and position in below tabletable data contains likenamesas programersas programermy name is raviiam sas programeriam good in sasbased on this i want out put like thispositionid name1 sas1 sas0 sas5 sas13 sas and i try this like select charindex('sas',name)from table .that time i find only postion of sas string but i want display name sas and that positionhow to solve this in sql server.plz tell me the query

Restoring SQl SERVER 2008 R2

Posted: 30 Apr 2013 12:52 AM PDT

Hi Guys:Any guide how to restore sql server 2008 r2 backup on sql server 2000thanxsymun

Peer-to-peer replication

Posted: 30 Apr 2013 01:23 AM PDT

Question. if I insert a row on one server in the peer-to-peer topology, is it possible that an update is attempted on that same row on another server before that insert has been replicated over? Meaning is it synchronous or asynchronous?

Negative SPID -4

Posted: 30 Apr 2013 01:14 AM PDT

Hello All,Can someone share some information on -4 SPID? I am aware about -2 and -3 but have never seen -4 and cant find any online resources regarding this. One of our client's server showed blocking by spid -4. They restarted the machine to resolve the issue so I dont have much info. However they had captured sysprocesses details which shows some processes were blocked by -4. I've asked for more details. Apparently they run some linked queries but as per my understanding that would result in -2 spid.Pls see attached.

Shrinking the log file does not reduce size

Posted: 29 Apr 2013 10:23 PM PDT

I have a database which had mdf size of 350 MB and ldf size 4.9 GBWhen i try to shrink the log file it's not shrinking. I know shrinking a database is not good and it should not be done. But still i am trying to do it for shrinking the log file. The recovery model is set to FULL.Then i followed some steps:When i run dbcc SQLPerf(logspace) i found that logsize is 4932 MB and Log space used is 98.76%So large amount of (98%) of log is using now.Then i run this command use <databasename> dbcc loginfoNow almost all VLF has status 2 which means all are in use.then I tried to take log backup. After log backup also shrinking didnt reduce the size. Then i changed recovery model to simple and then tried shrinking.But this also didn't help. Also i ran dbcc opentran (database)and found that no transaction is open now. So what is making the database which does not allow me to shrink the log size.How can i solve this?

SSIS - Excel 2010 - Provider=Microsoft.ACE.OLEDB.12.0

Posted: 30 Apr 2013 12:21 AM PDT

I am requesting that the Microsoft Access Database Engine 2010 Redistributable be installed on my SQL 2008R2 Enterprise SP1 running on Windows Server Enterprise 2008R2 SP1 but am getting denied the request because in the page to download the drivers (http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255) it states that:[i]The Access Database Engine 2010 Redistributable is not intended: 4.To be used by a system service or server-side program where the code will run under a system account, or will deal with multiple users identities concurrently, or is highly reentrant and expects stateless behavior. Examples would include a program that is run from task scheduler when no user is logged in, or a program called from server-side web application such as ASP.NET, or a distributed component running under COM+ services.[/i]The requirement is to be able to run an SSIS package that interrogates various MS Excel 2010 workbooks before converting them to csv. The conversion to csv is just a side line so please do not be distracted by that, rather focus on the need to be able to look at the excel tables and other content. The package should run on a SQL Agent job through a proxy and credential with the credential running under the context of a service account that itself will have very limited privilege however it maybe that the package will reside on the server as a physical file and executed directly from a front end under a regular user.Now because of this 'not intended' wording on the MS site my WINTEL support guys are denying my request but I have not been able to get any further information from them or in fact any where else as to why MS state this or what the issues might be. I just want a driver to be able to not just import from Excel but to dig deeper into the Excel file.Does anyone know why this driver set should not be installed or why MS make this statement.Thanks...

Use of rownum function in a select

Posted: 29 Apr 2013 08:37 PM PDT

HiI need to get the first row from a subselect. Have read a couple posts on this topic, but not sure I get it.Please find below the code.What I need is that the route subselect only returns the first record for every route. SELECT (SELECT OM.ord_route, [color=#F90A0A]rownum r[/color] FROM Trans T, Order OM WHERE T."From license number." = T1."From license number." AND T."Transaction type." = 014 AND OM.ord_key = T.ORD_KEY AND (T."Date record was created." > T1."Date record was created." and T."Date record was created." < T1."Date record was created." + 1) [color=#F90A0A]and r = 1[/color] ) as route, T1.TO_ITEM, T1.FROM_LOCATION, T1.TO_LOCATION,...............FROM Trans as T1 WHERE T1."From license number." like '4%'Any ideas?BRDan

Installing SQL Server Express 2008 & Studio on Windows 8

Posted: 29 Apr 2013 11:46 PM PDT

Hello all. My first post here. My question probably will seem amateur as I'm sure I is. I've been hired for a job based on my very old, rusty SQL Server skills. I have quiet a bit of experience using and developing with SQL Server 2000. Yep. 13 years ago.I'm trying to brush up on my skills and hit the books hard. I've purchased a new laptop running Windows 8 (ugggh...don't like it!!) and my intention was to install SQL Server 2008 Express and SQL Server Management Studio 2008 on this laptop so I can practice along with the chapters I'm reading. I'm using 2008 since that's what the project I'll soon be working on uses.Last night I was able to install SQL Server Express on this laptop (SP3 I think) but had no luck installing SQL Server 2008 Management Studio.Has anyone been able to use these two products on Windows 8?Or would you recommend I go to the SQL Server 2012 products?Thanks for helping out an old dog learn new tricks!:-)

SQL Script To Find Non Basic Latin

Posted: 29 Apr 2013 11:27 PM PDT

I'm having issues with Invalid Unicode charters in my 2008 DB, caused by people using copy and paste into the program front end and picking up carriage returns.I'm still learning sql and this is a little beyond me.Any ideas for a script that would search entire Database and locate any 'non basic Latin' characters and there locations so I can remove them.Many thanks in advance.CheersKev

Generating a counter table in SQL

Posted: 29 Apr 2013 09:54 PM PDT

Hi All,I am currently looking at a project that requires acounter table - I have done this very easilly in Excel with a VBScript - but want to do it with a SQL Script.Any thoughts ?[b]Sub updatevalues()Dim TotalDim CounterDim rowDim RecordNumberRecordNumber = 1Total = 1Counter = 1row = 2For Total = 1 To 300 For Counter = 1 To Total Sheets("Sheet1").Cells(row, 1).Value = RecordNumber Sheets("Sheet1").Cells(row, 2).Value = Total Sheets("Sheet1").Cells(row, 3).Value = Counter row = row + 1 RecordNumber = RecordNumber + 1 Next CounterNext TotalEnd Sub[/b]

VIEW vs. DynamicSQL

Posted: 29 Apr 2013 08:04 PM PDT

Hello,I have few questions, I would like remove DSQL, but our design table is not good, but redesign table is for future, because few programs ( korean source, sometimes we dont have source... long story) still use direct statement in source.So now we have separate table, but there is column line_code I dont know who designed this - Korean :Dso my questions is for someone who understand about query optimazing ...here is declare of one table , there is different just end of table for example CPM01, CPM02, according to help by opc.three I added check constraint for line code [quote]USE [MCS_MESDB]GO/****** Object: Table [dbo].[TB_MASTER_CPM01] Script Date: 4/30/2013 10:39:11 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[TB_MASTER_CPM01]( [ORDER_DATE] [char](8) NOT NULL, [COMMIT_NO] [char](4) NOT NULL, [BODY_NO] [varchar](12) NOT NULL, [CAR_CODE] [varchar](3) NOT NULL, [LINE_CODE] [char](5) NOT NULL, [STATION_ID] [char](5) NOT NULL, [WORK_SEQ] [int] NOT NULL, [POINT_SEQ] [int] NOT NULL, [POINT_SER] [int] NOT NULL, [WORK_TYPE] [varchar](2) NOT NULL, [ITEM_CODE] [char](5) NULL, [ALC_CODE] [varchar](4) NULL, [WORK_CODE] [varchar](6) NOT NULL, [WORK_POS] [char](1) NOT NULL, [WORK_QTY] [int] NOT NULL, [LIMITV_LOW] [decimal](5, 2) NULL, [LIMITV_HIGH] [decimal](5, 2) NULL, [OPTION_VALUE] [varchar](20) NULL, [MASTER_DESC] [varchar](50) NULL, CONSTRAINT [PK_TB_MASTER_CPM01] PRIMARY KEY CLUSTERED ( [ORDER_DATE] ASC, [COMMIT_NO] ASC, [BODY_NO] ASC, [CAR_CODE] ASC, [LINE_CODE] ASC, [STATION_ID] ASC, [WORK_SEQ] ASC)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[TB_MASTER_CPM01] ADD CONSTRAINT [DF_TB_MASTER_CPM01_POINT_SEQ] DEFAULT ((1)) FOR [POINT_SEQ]GOALTER TABLE [dbo].[TB_MASTER_CPM01] ADD CONSTRAINT [DF_TB_MASTER_CPM01_POINT_SER] DEFAULT ((1)) FOR [POINT_SER]GOALTER TABLE [dbo].[TB_MASTER_CPM01] ADD CONSTRAINT [DF_TB_MASTER_CPM01_WORK_QTY] DEFAULT ((0)) FOR [WORK_QTY]GOALTER TABLE [dbo].[TB_MASTER_CPM01] ADD CONSTRAINT [DF_TB_MASTER_CPM01_LIMITV_LOW] DEFAULT ((0)) FOR [LIMITV_LOW]GOALTER TABLE [dbo].[TB_MASTER_CPM01] ADD CONSTRAINT [DF_TB_MASTER_CPM01_LIMITV_HIGH] DEFAULT ((0)) FOR [LIMITV_HIGH]GOALTER TABLE [dbo].[TB_MASTER_CPM01] WITH CHECK ADD CONSTRAINT [check_CPM01] CHECK (([LINE_CODE]='CPM01'))GOALTER TABLE [dbo].[TB_MASTER_CPM01] CHECK CONSTRAINT [check_CPM01]GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ORDER DATE' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TB_MASTER_CPM01', @level2type=N'COLUMN',@level2name=N'ORDER_DATE'GO[/quote]here is view [quote]USE [MCS_MESDB]GO/****** Object: View [dbo].[VIEW_MASTER] Script Date: 4/30/2013 10:55:50 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW [dbo].[VIEW_MASTER] WITH SCHEMABINDING AS SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_AGVENUNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_AGVF1UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_AGVF2UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_AGVFAUNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_AGVFCUNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_AGVRCUNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_CPM01UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_CPSB1UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_ENG01UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_FAST1UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_FAST2UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_FAX01UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_FAX02UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_FCM01UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_FEM01UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_RCM01UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_TRCP1UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_TRCP2UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_TRFE3UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_TRSB1GO[/quote]And here I have few testing queries [quote]DECLARE @LINE_CODE VARCHAR(5)='CPM01'DECLARE @COMMIT_NO varchar(4)='0042'DECLARE @ORDER_DATE varchar(8)='20130301'DECLARE @STATION_ID varchar(5)='CP-08'DECLARE @sql varchar(2000)DECLARE @nsql nvarchar(2000)DECLARE @StartTime datetimeSET @StartTime = GETDATE()select TOP 1 * from [VIEW_MASTER] WHERE ORDER_DATE=@ORDER_DATE AND LINE_CODE=@LINE_CODE AND COMMIT_NO=@COMMIT_NO AND STATION_ID=@STATION_ID OPTION(RECOMPILE);SELECT ExecutionTimeInMSVieWVariableOPt = DATEDIFF(millisecond, @StartTime, getdate()) SET @StartTime = GETDATE()select TOP 1 * from [VIEW_MASTER] WHERE ORDER_DATE=@ORDER_DATE AND LINE_CODE=@LINE_CODE AND COMMIT_NO=@COMMIT_NO AND STATION_ID=@STATION_ID ;SELECT ExecutionTimeInMSVieWVariable = DATEDIFF(millisecond, @StartTime, getdate()) SET @StartTime = GETDATE()select TOP 1 * from [VIEW_MASTER] WHERE ORDER_DATE=@ORDER_DATE AND LINE_CODE='CPM01' AND COMMIT_NO=@COMMIT_NO AND STATION_ID=@STATION_ID ;SELECT ExecutionTimeInMSVieWwithoutVar = DATEDIFF(millisecond, @StartTime, getdate()) SET @StartTime = GETDATE()set @nsql='SELECT TOP 1 * FROM [VIEW_MASTER] WHERE ORDER_DATE=@ORDER_DATE AND LINE_CODE=@LINE_CODE AND COMMIT_NO=@COMMIT_NO AND STATION_ID=@STATION_ID'EXECUTE sp_executesql @nsql, N'@ORDER_DATE varchar(8), @LINE_CODE varchar(5), @COMMIT_NO varchar(4), @STATION_ID varchar(5)' , @ORDER_DATE, @LINE_CODE, @COMMIT_NO, @STATION_IDSELECT ExecutionTimeInSP_EXECUTESQLView = DATEDIFF(millisecond, @StartTime, getdate())SET @StartTime = GETDATE()set @nsql='SELECT TOP 1 * FROM TB_MASTER_'+@LINE_CODE+' WHERE ORDER_DATE=@ORDER_DATE AND LINE_CODE=@LINE_CODE AND COMMIT_NO=@COMMIT_NO AND STATION_ID=@STATION_ID'EXECUTE sp_executesql @nsql, N'@ORDER_DATE varchar(8), @LINE_CODE varchar(5), @COMMIT_NO varchar(4), @STATION_ID varchar(5)' , @ORDER_DATE, @LINE_CODE, @COMMIT_NO, @STATION_IDSELECT ExecutionTimeInSP_EXECUTESQL = DATEDIFF(millisecond, @StartTime, getdate()) SET @StartTime = GETDATE()set @sql='SELECT TOP 1 * FROM TB_MASTER_'+@LINE_CODE+' WHERE ORDER_DATE='''+@ORDER_DATE+''' AND COMMIT_NO='''+@COMMIT_NO+''' AND STATION_ID='''+@STATION_ID+''''execute (@sql)SELECT ExecutionTimeInMSDynamic = DATEDIFF(millisecond, @StartTime, getdate())SET @StartTime = GETDATE()select TOP 1 * from TB_MASTER_CPM01 WHERE ORDER_DATE=@ORDER_DATE AND COMMIT_NO=@COMMIT_NO AND STATION_ID=@STATION_IDSELECT ExecutionTimeInMSQuery = DATEDIFF(millisecond, @StartTime, getdate())[/quote]FOR TEST you can add to table just one row with parameteres of variable, another columns you can add random..And next I used this command for better result [quote]CHECKPOINT; GO DBCC DROPCLEANBUFFERS; DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHEGOselect plan_handle, creation_time, last_execution_time, execution_count, qt.textFROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt[/quote]but the background of this I dont understand very well.very thank you for response

Discriminator Field

Posted: 29 Apr 2013 07:39 PM PDT

Hi could anyone tell me whya discriminator column would be added to a table?what is a discriminator column? what is it used for?thanks in advance
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog