Monday, May 27, 2013

[SQL Server 2008 issues] Database Tuning Advisor

[SQL Server 2008 issues] Database Tuning Advisor

Database Tuning Advisor

Posted: 26 May 2013 05:26 PM PDT

Hi all, Recently i have been assigned a task to optimize the performance of the database. I decide to run profiler and done the same and captured the SP's and give the trace as work load for DTA . DTA analysed the trace and come up with so many recommendations. So what we have to do now. Is there any option that automatically applies all the recommendations from DTA. Is it trustworthy to implement all the suggestions from DTA.

sqlcmd

Posted: 26 May 2013 02:51 PM PDT

Hi,could any one pls help me ? am scratching my head with sqlcmd.this the issuecreate table testcmd (a int identity, b varchar(10))goinsert into testcmd values ('a,b,c')running below sqlcmdsqlcmd -SMyservername -E -q"select b from testcmd" >"files.csv" -s"," -W -w200Column B has comma separated value, so in csv, it breaks upto three column.s how to do this for -s?

HI need some answers for these questions

Posted: 26 May 2013 05:31 PM PDT

HI ,all 1) What is cluster service ?2) What is quorum ? 3)What are types of quorum ?4) What you means private and public network ?5) What is difference between install SQL server 2005 and SQL server 20086) What is difference between the troubleshooting in SQL server 2005 and SQL Server 2008 while installation .7) How is internal behavior of SQL server 2005 and SQl server 20088)What is means of Virtual IP Address and Virtual Name9) Max number of instance instance in SQL server 2005 and 200810) Service pack applying in SQl server 2005 and 2008 11)How internal structure of Quorum?12)Why is means by MSDTC and It is mandatory for sql server 2005 and 2008 13)How you validating the SQL Cluster ?14) If your cluadmin is not working then how you validating the whether your resources are online or not15) What means by prepared owner and possible owner ?16) What are ip address required when you installing 3 node cluster with 2 instance

Curious Case of .. Syntax Error. Or may be not?

Posted: 26 May 2013 03:18 AM PDT

Not sure- to laugh or cry :-)I created small SP to rebuild/reorganize indexes on sql server (2008 R2) DBs (only for indexes that require this action certainly)Essentially it's simple cursor- during each iteration (let say 100 indexes to rebuild from 5 different DBs) db_name/tbl_name/index are extracted from ahead prepared table (tblMain) to create dynamic string and execute it. Each iteration completes with tblMain update to report particular index action completion ([i]done = 1, start/end = GETDATE()). [/i]Simple like truth. Now the tricky part. When SP complets (all 100 indexes rebuilt or reorganized successfully, i.e. every one has [i]done = 1[/i]) SP returns.. [b]error[i][/i][/b]: "Msg 102, Level 15, State 1, Line 1; Incorrect syntax near '[b]([/b]'."Oops :w00t:  If there is syntax error then SP should not start at all- correct? Where is this mystery '[b]([/b]' coming from? Funny enough but the only place where this '[b]([/b]' bracket used is GETDATE[b]([/b]) function.But it was used 100 times for 100 indexes to update start/end date in tblMain without any problem?!It's certainly not syntax error IMO, but what is it? Intensive Google search returns nothingLooking for a help (any clue) from the "best of the best" (dead serious)Thanks in advanceYuri

Fragmentation accuracy? 98% fragmented?!!

Posted: 26 May 2013 03:14 AM PDT

Hi guys,Today/2am this morning I received my first call out (yay!) for a terrible server I hadn't heard of before. Besides the main poor points (backups on the same drive as datafiles, no compression & a filegroup dedicated to logging user activity somehow taking up 120GB over the past 1 month when the total database is 200gb including the 120gb logging; so 80gb without) I also looked at the filegroups dedicated to indexes. What I found was surprising & leads me to believe it's inaccurate, the following query:[code]SELECT ps.database_id, ps.OBJECT_ID,ps.index_id, b.name, ps.avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS psINNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_IDAND ps.index_id = b.index_idWHERE ps.database_id = DB_ID()ORDER BY ps.OBJECT_ID[/code] Lead to values like (note: I only copied the index names & fragmentation columns):[code]I_SEM_AGENT_USN 90.5616676317313I_SEM_AGENT_COMP 98.3505154639175I_SEM_AGENT_GRP 86.5384615384615I_SEM_AGENT_RT 76.453488372093I_SEM_AGENT_ID_PLUS 88.4057971014493I_SEM_AGENT_PATTERN 97.7766013763896I_SEM_AGENT_AGENT_VERSION 85.8789625360231I_SEM_AGENT_STATUS 99.2708333333333I_SEM_AGENT_AGENT_ID 98.9637305699482[/code]Judging by this it would lead me to believe that only 1-2% of each page is being used? Which seems a little strange to me...does anybody notice an error in the query that may lead to the values being wrong? Or this column isn't supposed to be trusted? Or I need to be doing some rebuilds next weekend? :PDird

Is there any book in market on major/common SQL Server 2005/2008 errors and its possible solutions ?

Posted: 25 May 2013 09:47 PM PDT

Hello,Is there any book in market on major/common SQL Server 2005/2008 errors and it's possible solutions ?Just curious to know.Thanks,San.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog