Tuesday, August 13, 2013

[SQL 2012] Performace issue Physical vs Virtual

[SQL 2012] Performace issue Physical vs Virtual

Performace issue Physical vs Virtual

Posted: 12 Aug 2013 10:07 AM PDT

I've got the following issue with a simple [code="sql"]select * from large_table[/code] running on SQLServer 2012. The physical machine (Production) returns the result set in approx 3.5 mins, the virtual (UAT) returns in 1.8 mins. The table size is equivalent in both environments. The physical machine that hosts the virtual machine is exactly the same spec as the physical machine so use the same CPU's.I've made sure there's no physical I/O going on, all the reads are logical.A breakdown of the CPU/elapsed times are: Production UATCPU Times (ms) 18220 11279Elps Time (ms) 232879 110659So it looks like both environments are waiting on something as there's a big difference between CPU and Elapsed time. I've then traced the waits for the sessions involved and got the following results Production UATNETWORK_IO 19807 96869PREEMPTIVE_OS_WAIT_FORSINGLEOBJECT 19727 95873 So the waits in UAT are much larger than Production where its slow????I'm fairly new to SQL Server, coming from an Oracle background so maybe my wait collection technique is wrong. I've used event sessions to capture the waits i.e.[code="sql"]create event session session_waits on serveradd event sqlos.wait_info(WHERE sqlserver.session_id=102 and duration>0), add event sqlos.wait_info_external(WHERE sqlserver.session_id=102 and duration>0)add target package0.asynchronous_file_target (SET filename=N'c:\temp\wait_stats.xel', metadatafile=N'c:\temp\wait_stats.xem');[/code]Any help/ideas on what could be going would be much appreciated.Thanks,Fraze

Put 4 tables in 1 table.. HOW to do that?

Posted: 12 Aug 2013 09:50 PM PDT

I have just make a new database and im trying to put 4 tables in 1 table. I heard you most use the INTO syntax...So i was doing firts this:SELECT *INTO deviceFROM WhatsUp_Assyst_replicatie2.dbo.Device;So the rows are in the table (Device) but when im trying to put the other table form other datebase in the same table it gives a error;Msg 2714, Level 16, State 6, Line 1There is already an object named 'device' in the databasehow to fix that and make sure that the tables comes all in 1 table.. :Stnx

DBmail stopped working

Posted: 27 Jun 2013 11:42 PM PDT

Hi,I have a newMicrosoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Oct 19 2012 13:38:57 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)While trying to alter sp_db_mail, my dbmail stopped working. I am getting below error in logs:Message1) Exception Information===================Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseExceptionMessage: The Transaction not longer valid.Data: System.Collections.ListDictionaryInternalTargetSite: Void ValidateConnectionAndTransaction()HelpLink: NULLSource: DatabaseMailEngineStackTrace Information=================== at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.ValidateConnectionAndTransaction() at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.RollbackTransaction() at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.GetDataFromQueue(DataAccessAdapter da, Int32 lifetimeMinimumSec) at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.ProcessQueueItems(String dbName, String dbServerName, Int32 lifetimeMinimumSec, LogLevel loggingLevel, Byte[] encryptionKey, Int32 connectionTimeout)Steps I have tried after this issue:1) Recreated send_db_mail procedure from instance running on 20122) Stopped and Started DB mail using sysmail_stop_sp and sysmail_start_sp3) Checked that service broker is running4) SMTP is working fine as DBMAIL using this SMTP on other server are running fine5) Deleted all unsent emails6) Rebooted the server/services/agent7) Anti-virus also seems to be okay as only this server is throwing errors8) Replaced databasemail.exe and other *.dll's from other 2012 serverAny help would be appreciated!Thanks

SSMS Slow Open

Posted: 12 Aug 2013 11:27 PM PDT

Is it normal for SSMS 2012 to take about 42 seconds to open on a workstation?Windows 7 Enterprise x64Intel(R) Core(TM) i5 CPU, M540 @ 2.53GHz 2.53GHz4.00 GBSSMS Version 11.0.3128.0I've applied all Windows updates to my SQL server.After closing SSMS and re-opening it only takes about 11 seconds. It seems to be the first start-up.

Cleaning Up a Table

Posted: 12 Aug 2013 11:08 PM PDT

I have a list of contacts approx 50,000 records. 2 Problems:1)Duplicates: Company name, etc... Ph # Fax # etc...Some of the Dups may be 'Wiley Company' AND 'The Wiley Co.' How do i run a delete command to get rid dup Companies. 2) The Column w/ ph/fx numbers is a smallint, but i noticed after importing that obviously non-int's were imported into the column, like (405)322-2013. how can i clean up this column and convert the data to small ints? Also, want to get rid of duplicate ph/fx numbers at that point as well. any help would be greastly appreciated!f

Update takes long time

Posted: 12 Aug 2013 10:44 PM PDT

We have to update table with 160 mln rows, this is 3 years data. In production it could be more. Now I'm trying to run following script, it fails after 3 days saying that it was timed out:[code="sql"]update SalesDateset [Class]= i.class, Department=i.dept, Division=i.div, Subclass=i.subclassfrom SalesDate s inner join Item i on i.Item_SK=s.Item_SK where s.dateid>='20110201' and s.dateid<='20110228'go [/code]I've 28 queries for 3 years data (as I still didn't get the concept of how to use loop). Is there any way to improve this script so that it can be committed faster? I have created index on dateid and item_sk

Defining a trace to capture sp_executesql with many procs called

Posted: 12 Aug 2013 06:49 PM PDT

Hello - having a performance issue narrowed to within a few rpc calls via sp_executesql. Please - no recommendations to change the code - I don't own it and it won't happen soon. In the meantime, I have:SET NOCOUNT ONBEGIN TRYBEGIN TRAN;EXEC [dbo].[PROC1] 155544325,83876715...EXEC [dbo].[PROC2] 1055,''24000/HC213''EXEC [dbo].[PROC3] 155544325,0,5729925....COMMIT TRANEXEC [dbo].[PROC4] @ThingyID_3 out,@ExtPersonId_3 out...EXEC [dbo].[PROC5] @ThingyID_3,@FixClThingyID_4 out...EXEC [dbo].[PROC6] @AccountID_3,@ClientID_5 out...EXEC [dbo].[PROC7] @ClientID_6 out,155544325...EXEC [dbo].[PROC8] @ThingyID_3,@ToOpenClose_7 ...END TRYBEGIN CATCH IF XACT_STATE() <> 0 rollback; DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT; SELECT @ErrorMessage = dbo.GetErrorInfo(''''), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); if @ErrorState = 0 set @ErrorState = 1 RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);END CATCH;This is just one example - but one of these procs loses it now and then - and I'd like to get a handle on what the durations, reads, etc are that will spike execution times for a spell. The problem I see is profiler could never provide such granularity. Its 2012 instance, so I have events with more options. Wondering if someone has seen this so I can fast track a solution.

SQL Server Books online

Posted: 12 Aug 2013 01:20 PM PDT

I have a tutorial for 2008 which indicates SQL Server Books Online is installed w/ sQL Server, I don't see that installed anywhere for 2012. Does anyone know if it's still installed and, if so, where?thxf

Executing a parameterized stored procedure from Excel

Posted: 12 Aug 2013 06:57 AM PDT

I'm using Excel 2010 (if it matters), and I can execute a stored procedure fine - as long as it has no parameters.Create a new connection to SQL Server,then in the Connection Properties dialog, specify Command Type: SQLCommand Text: "SCRIDB"."dbo"."uspDeliveryInfo"but if I want to pass a parameter, I would normally do something likeSCRIDB.dbo.uspDeliveryInfo @StartDate = '1/1/2010', @EndDate = GETDATE()but in this case, I would want to pass the values from a couple of cells in the worksheet. Do I have to use ADO (so this isn't a SQL Server question at all?)Sorry, searched around all over here, and couldn't find an answer, so I thought I'd ask.Thanks!Pieter

Multiple Availablity Groups on One Instance

Posted: 12 Aug 2013 09:44 AM PDT

From what I understand, one SQL Server 2012 instance can host multiple availability groups. That should allow, for example, the following situation - one SQL Server 2012 instance containing three primary replica databases - each one part of a different availability group - and each availiability group's secondary replica located on a separate SQL Server 2012 instance. Does it work the other way around as well? Can you have three SQL Server 2012 instances, each with one primary replica database and have all three secondary replica databases on one SQL Server 2012 instance? So instead of, as above, going from 1 primary server to 3 secondary servers, this time we're going from 3 primary servers to 1 secondary server? The one secondary server would then contain all three secondary replicas for each of the 3 separate primary replicas.This would mean that the single server (where all three secondary replicas reside) was part of three separate Windows Server Failover Clustering clusters. Is this scenario possible?Thanks for any insight. Willem

Average time to two decimal places in Expression

Posted: 12 Aug 2013 08:57 AM PDT

Hi Experts,can you please help me in making the following expression to two decimal places=Avg(Fields!TimeInSec.Value, "TotalTime") & " Seconds"

Viewing Table ?

Posted: 12 Aug 2013 04:34 AM PDT

Just got 2012 Developer and a Book says I should be able to view a Tables contents by right/click 'open table' menu option. But the closest thing I show is 'select top 1000 rows'. Anyone have suggestions?thxf

Same Query, Same Hardware, Same Data, different Plan. drastically different CPU Actual Costs and Duration.

Posted: 12 Aug 2013 04:29 AM PDT

Attached both plans. Both - Same Hardware (different machines)- Same data in/out of query.- Nearly identical instance configurationsDifferences I note- On the two db's, there are some changes in indexes. I get near identical results on the dev machine on a near exact copy of the db and an earlier version with about 20 fewer indices.- On the two instances, Prod is capped at a bit over 100gb of memory and doesn't use it all. Dev is not capped and does not use it all.- Prod has 1 larger db, but it's inactive. It also has about 10 smaller dbs that are mostly inactive.- Indexes and stats on prod (the slower of the two) are very up to date. On dev, they're the same in one db and a few months out of date in another. Still dev is doing a better job with the plan.It's odd, it just looks like the plan chosen on Prod is slightly different and then gets slammed on CPUWith the plans there are slight differences in duration all around, but Statement3 shows a duration of 51,167 vs 4,310. A CPU of 47,861 vs 3760.I'm using SQL Sentry Plan Explorer as well as SSMS to compare the plans. Both show high durations/CPU overall, but don't have a whole lot of that information within the statement of the plan itself. Both Statement3 plans (attached as image) look identical. But Production took 10x longer and 10x more cpu.Leads me to believe that they both made the same query optimization choices, but it wasn't a good plan for production because it handles something differently with CPU.What else would explain this sort of difference in how the CPU is utilized?I'm not even sure if looking at this in SQL is a deadend if it's an actual windows level difference between the two servers.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog