Wednesday, October 2, 2013

[SQL 2012] SQL Wait type xe_live_target_tvf

[SQL 2012] SQL Wait type xe_live_target_tvf


SQL Wait type xe_live_target_tvf

Posted: 02 Oct 2013 02:58 AM PDT

Hi All,We're having some performance problems with our server, and so checked the wait stats. When I run the following script, taken from Glenn Barry's, 2012 Diagnostic Information Queries, the highest wait stat, by an order of magnitude, is XE_LIVE_TARGET_TVF. We're running SQL 2012 x64 Standard on a Win2k8 machine. I can't find any information about this wait, and it's not documented on the msdn dm_os_wait_stats page. Can anyone tell me if it is relevant or just another wait that should be excluded? [code="sql"]WITH WaitsAS (SELECT wait_type, CAST(wait_time_ms / 1000. AS DECIMAL(12, 2)) AS [wait_time_s], CAST(100. * wait_time_ms / SUM(wait_time_ms) OVER () AS decimal(12,2)) AS [pct], ROW_NUMBER() OVER (ORDER BY wait_time_ms DESC) AS rn FROM sys.dm_os_wait_stats WITH (NOLOCK) WHERE wait_type NOT IN (N'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP', N'RESOURCE_QUEUE',N'SLEEP_TASK', N'SLEEP_SYSTEMTASK', N'SQLTRACE_BUFFER_FLUSH', N'WAITFOR', N'LOGMGR_QUEUE', N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH', N'XE_TIMER_EVENT', N'BROKER_TO_FLUSH', N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT', N'CLR_AUTO_EVENT', N'DISPATCHER_QUEUE_SEMAPHORE' ,N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'XE_DISPATCHER_WAIT', N'XE_DISPATCHER_JOIN', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'ONDEMAND_TASK_QUEUE', N'BROKER_EVENTHANDLER', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'DIRTY_PAGE_POLL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',N'SP_SERVER_DIAGNOSTICS_SLEEP')),Running_Waits AS (SELECT W1.wait_type, wait_time_s, pct, SUM(pct) OVER(ORDER BY pct DESC ROWS UNBOUNDED PRECEDING) AS [running_pct] FROM Waits AS W1)SELECT wait_type, wait_time_s, pct, running_pctFROM Running_WaitsWHERE running_pct - pct <= 99ORDER BY running_pctOPTION (RECOMPILE);[/code]

FULLTEXT search

Posted: 02 Oct 2013 02:57 AM PDT

Hi,I came across a FULLTEXT catalog that documents tables in a database.The catalog has 5 descriptions tables (table, columns, triggers, foreign keys and check constraints).If I want to search for Sales and Product the SalesDetails table doesn't show on the results cause it's being built like this:[code="SQL"]SELECT t0.Id, ct.Rank FROM CONTAINSTABLE(DescriptionsTables, *, '"*sales*" AND "*products*"') ct INNER JOIN DescriptionsTables t0 ON tc.[Key] = t0.IdUNION ALLSELECT t0.TableId, ct.Rank FROM CONTAINSTABLE(DescriptionsTablesColumns, *, '"*sales*" AND "*products*"') ct INNER JOIN DescriptionsTablesColumns t0 ON tc.[Key] = t0.Id.....[/code]Since the text "sales" is only in tables descriptions and "products" in tables columns descriptions, never on both at the same time, the table doesn't show on the results...There is a way, that I know of, to make this work... Create a table whenever DescriptionsTables is changed (trigger) and populate it with the several tables' data and use it for the fulltext instead of the other 5....But is there an easy way to do this? Merge all the catalogs in just one for the search?! (probably stupid question since the reference for the KEY column is different on all the catalog tables...).Thanks,Pedro

ADODB Recordset Updating

Posted: 02 Oct 2013 02:48 AM PDT

The following MSAccess VBA code worked well with SQL Server 2005, but produces an error with SQLServer 2012.The error is #3251 "Current recordset does not support updating". Set gCnn = New ADODB.Connection gCnn.ConnectionString = "Driver={SQL Server Native Client 11.0};Server=COMPUTER-A\NORMARMSSERVER;DSN=ArmsdataDSN;Database=ArmsData;UID=armsdb;PWD=EQ23234AZ;Trusted_Connection=yes;" gCnn.Open Dim rst As ADODB.Recordset Dim sql As String Set rst = New ADODB.Recordset rst.CursorLocation = adUseClient sql = "SELECT * FROM tblQuoteLob WHERE (QuoteID = " & Me!QuoteID & ")" rst.Open sql, gCnn, adOpenKeyset, adLockOptimistic If rst.EOF And rst.BOF Then 'add a new record rst.AddNew rst!QuoteID = Me!QuoteID rst.Update End If rst.Close Set rst = Nothing Any Ideas?

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Posted: 02 Oct 2013 12:45 AM PDT

Where can i place my ORDER BY statement to sort ALL the data from the table, not just the 'from' and 'to' records?Everywhere i try to move it, i get the above error.Thanks for any help! USE [JobPortal9_10_13]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- ============================================= -- -- Description: <Description,[Get All Jobs Searched Structured SQL],> -- -- ============================================= alter Procedure [dbo].[zianGetAllJobsSearched10_1_13FAST] -- Add the parameters for the stored procedure here @Title varchar(250), @Industry int, @Industry2 int, @Industry3 int, @Date int, @JobTitle int, @JobType int, @Experience int, @Education int, @State int, @City int, @Salary int, @MaxSalary int, @fromRec int, @toRec int, @OrderBy VARCHAR(50), @OrderByDirection VARCHAR(10) WITH RECOMPILE AS DECLARE @ActualDate DateTime = cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20));IF (@Title = '') SET @Title = NULL;WITH EMPLOYMENT_OPPORTUNITIES_CTE(id,title,contactperson,lastmodified,description,workexperiencetypeid, workexperience,jobtypeid,AcademicExperienceTypeId, industryid,industryid2,industryid3,salaryminid,salarymaxid, --jobTitle, city,state, PostalCode,positions,lastmodified2) --,deadline)AS( SELECT e.id,title,contactperson,lastmodified,e.description,workexperiencetypeid, isnull((select we.[Name] from workexperiencetypes we where we.id=e.workexperiencetypeid),'') as workexperience, isnull((select jot.[Name] from jobtypes jot where jot.id=e.jobtypeid),'') as jobtype, isnull((select edu.[Name] from Degree edu where edu.Id=e.AcademicExperienceTypeId),'') as education, isnull((select ind.[Name] from industries ind where ind.id=e.industryid),'') as industryname, isnull((select ind.[Name] from industries ind where ind.id=e.industryid2),'') as industryname2, isnull((select ind.[Name] from industries ind where ind.id=e.industryid3),'') as industryname3, salaryminid,salarymaxid, --isnull((select jt.[Name] from jobTitles jt where jt.id=e.jobtypeid),'') as jobTitle, isnull((select ci.[Name] from cities ci where ci.id=c.cityid),'') as city, isnull((select r.[AbbreviatedName] from regions r where r.id=c.regionid),'') as state, isnull((select comp.[PostalCode] from Companys comp where comp.Id=c.id),'') as PostalCode, positions, substring(cast(e.LastModified as varchar(20)),1,12) as lastmodified2 --ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNumber from EmploymentOpportunities e With (nolock) --left outer join companys c on e.officeid=c.id --inner join companys c on e.officeid=c.id inner join companys c on e.companyid=c.id where (@Title IS NULL or title = @Title) and (@Industry = 0 OR e.industryid = @Industry) and (@Industry2 = 0 OR Industryid2 = @Industry2) and (@Industry3 = 0 OR Industryid3 = @Industry3) and (@Date = 0 OR lastmodified >= @Date) and lastmodified is not null and lastmodified > @ActualDate --and e.LastModified >''' + cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20)) + '''' --and (@JobTitle = 0 OR title = @JobTitle) and (@JobType = 0 OR jobtypeid = @JobType) and (@Experience = 0 OR workexperiencetypeid = @Experience) and (@Education = 0 OR academicexperiencetypeid = @Education) and (@State = 0 OR c.RegionId = @State) and (@City = 0 OR c.CityId = @City) and (@Salary = 0 OR SalaryMinID >= @Salary) and (@MaxSalary = 0 OR SalaryMaxID <= @MaxSalary) )SELECT id,title as Title,contactperson as ContactPerson,lastmodified,description as Description,workexperience,jobtypeid as jobtype,academicexperiencetypeid as education,industryid as industryname,industryid2 as industryname2,industryid3 as industryname3,salaryminid as salary,salarymaxid as maxsalary,--jobtitle as jobTitle,city + ', ' + state + ', ' + PostalCode as Location,positions as Positions,--deadline,rownumber as RowNumber,(select COUNT(*) from EMPLOYMENT_OPPORTUNITIES_CTE) as TotalCount FROM ( SELECT id,title,contactperson,lastmodified,description,workexperience,jobtypeid,academicexperiencetypeid,industryid,industryid2,industryid3,salaryminid,salarymaxid,--jobtitle,city,state,PostalCode,--Location,positions,--deadline,ROW_NUMBER() OVER (ORDER BY LastModified DESC ) RowNumber FROM EMPLOYMENT_OPPORTUNITIES_CTE ) p1WHERE RowNumber BETWEEN @fromRec AND @toRec ORDER BY CASE WHEN @OrderBy = 'LastModified' AND @OrderByDirection = 'D' THEN LastModified END DESC, CASE WHEN @OrderBy = 'LastModified' AND @OrderByDirection != 'D' THEN LastModified END, CASE WHEN @OrderBy = 'City' AND @OrderByDirection = 'D' THEN City END DESC, CASE WHEN @OrderBy = 'City' AND @OrderByDirection != 'D' THEN City END, CASE WHEN @OrderBy = 'State' AND @OrderByDirection = 'D' THEN State END DESC, CASE WHEN @OrderBy = 'State' AND @OrderByDirection != 'D' THEN State END, CASE WHEN @OrderBy = 'Title' AND @OrderByDirection = 'D' THEN Title END DESC, CASE WHEN @OrderBy = 'Title' AND @OrderByDirection != 'D' THEN Title END OPTION(Maxdop 8)

"could not open error log file" on incorrect SQL cluster node

Posted: 01 Oct 2013 11:52 PM PDT

Hi. We have 2-node SQL 2012 cluster running as VMs in Hyper-V. The cluster hosts 4 SQL instances, running in active-active mode (2 instances per node).For reference, let's call instances Instance1-4 and nodes Node1-2.The problem: Instance3 is currently running on Node2 and there are no errors related to this instance in eventlog on Node2. However on Node1, the application eventlog is repeatedly spammed by events with ID 17058 with the MSSQL$Instance3 name as the source. The error is "initerrlog: Could not open error log file 'K:\MSSQL11.Instance3\MSSQL\Log\ERRORLOG'. Operating system error = 3(The system cannot find the path specified.)."That error makes sense, since the instance is running on Node2 and therefore the disk K: is currently accessible/mapped to Node2 and not Node1.The question is, why does this error appear on Node1 and where is it coming from??Thanks in advance

Sybase ASE 12 to SQL Server 2012 via SSMA

Posted: 01 Oct 2013 08:14 PM PDT

were using Sybase ASE 12.5 32 bit to SQL server 2012 64 bit via SSMA. I'm going to transfer only the Data since I'm done converting the schemas and recreating the tables. The problem is. When I click the migrate button I get this error. A really generic error and really frustrating one since the logs shows no signs of errors understandable by human. Anyone having problems with this one also? been stock here for weeks. [Gui: Error] [16260/16] [2013-10-02 04:00:17]: Exception: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE))[Datamigrator: Error] [16260/15] [2013-10-02 04:23:13]: Exception: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)) site: IErrorInfo GetErrorInfo(Int32, Int32) source: System.Data at System.Data.Common.UnsafeNativeMethods.IErrorRecords.GetErrorInfo(Int32 ulRecordNum, Int32 lcid) at System.Data.OleDb.OleDbError..ctor(IErrorRecords errorRecords, Int32 index) at System.Data.OleDb.OleDbErrorCollection..ctor(IErrorInfo errorInfo) at System.Data.OleDb.OleDbException.CreateException(IErrorInfo errorInfo, OleDbHResult errorCode, Exception inner) at System.Data.OleDb.OleDbConnection.ProcessResults(OleDbHResult hresult, OleDbConnection connection, Object src) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForMultpleResults(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteScalar() at Microsoft.SSMA.Framework.Generic.Utilities.DbUtilities.DbCommandWrapper.ExecuteScalar() at Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbCommand.Execute[T](ExecutionDelegate`1 executionMethod) at Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbCommand.ExecuteScalar() at Microsoft.SSMA.Framework.Sybase.SqlServer.DataMigration.TableDataMigrator.GetTableRowCount(String tableName, IDbConnection connection) at Microsoft.SSMA.Framework.Sybase.SqlServer.DataMigration.TableDataMigrator.GetSourceTableRowCount(String tableName, IDataMigrationContext context) at Microsoft.SSMA.Framework.Sybase.SqlServer.DataMigration.TableDataMigrator.GetSourceTableRowCountSafe(String tableName, IDataMigrationContext context)

importing a populating csv file

Posted: 01 Oct 2013 06:54 PM PDT

I have a CSV file which is getting information about every 30 seconds from a third party program and I want that data to be imported into my SQL DB. I tried using the import export wizard but it didn't like it because the file was being used by the third party program.Is anyone able to provide any insights? Apologies for my noobyness, I am brand new to SQL!

Need A Tool to Reverse Engineer an ERD

Posted: 01 Oct 2013 08:09 AM PDT

Our friends have done us a solid with Visio. Since SQL 2005 and Visio 2007 I have been reverse engineering ERD's from existing databases. We recently upgrade our SQL Server from 2008 R2 to 2012. We were successfully reverse engineering the 2008 R2 databases (after installing Visio 2010 SP1). Now we cannot reverse engineer the ERD. Thinking, like previous versions of Visio, that is was because the database engine was built after the Visio release I upgraded to Visio 2013.Silly me. [size="2"][b]Database Reverse Engineering has been deprecated in Visio 2013. Standard and Professional. [/b][/size]What tools are you in the community using to reverse engineer your SQL 2012 database? What is your opinion of those tools? I will not use the diagram tool in SSMS. For the simple reason of it is live in the database. A change in the diagram becomes a change in the database.(Yes, I know the model should be built before the DB. etc. But the devs rarely do that. And because I am the DBA it falls to my team to build ERD's. Otherwise we fail our annual IT audits.don't you love the life of a DBA? :hehe:)

No comments:

Post a Comment

Search This Blog