Saturday, April 6, 2013

[SQL Server 2008 issues] peformance analysis regarding temp db

[SQL Server 2008 issues] peformance analysis regarding temp db

peformance analysis regarding temp db

Posted: 05 Apr 2013 06:11 PM PDT

how to know(analyse performance) how many tempdb files creation are efficient?

Enabling Trace Flag T1118

Posted: 25 Mar 2013 04:17 PM PDT

Hi, We are using Sql server 2008 R2 express. We heard enabling T1118 is good for tempdb contention.Somewhere it said its not much help sql server 2005 aftwrwards. We are thinking about enabling it. Is there any disadvantages of enabling T1118?Thank You

how to delete my temp tables in batch?

Posted: 05 Apr 2013 03:23 AM PDT

Hello - Sometimes I create temp tables as part of running ad hoc queries.How long does SS keep temp tables? I seem to remember that temp table references are stored in sys.objects. How can I query sys.objects (or other appropriate table) to get a list of the temp tables I've created?I'm assuming I can delete my temp tables from the same table in which my temp table references are stored by using a standard delete statement?

Snapshot, Replication...something to get OpenEdge into SQL 2008 R2

Posted: 03 Apr 2013 12:48 AM PDT

I would love to setup a transactional replication between OpenEdge 11 and SQL 2008 R2...if possible.I have OpenEdge setup as a linked server and can access the tables...progress ODBC connection.I tried to create a SSIS package to at least get a snapshot nightly, but there is a problem with it seeing datatype 23...I then tried to add a convert piece to the SSIS package...still failed.I then tried to add a category listing so 23 = date in the mapping XML files...still failed.There are several tables and I find it strange that I can do one Table: Select * Into <sqltable> from <linkedserver...table>.....and this works AND pulls the date field with no error like the error above.I obviously do not want to do this for each table.Goal 1: get a snapshot of the linked server tables into SQL nightlyGoal 2: try to setup a transactional replicationAny help is greatly appreciated.

Convert file from ANSI to UNICODE

Posted: 05 Apr 2013 01:10 AM PDT

Currently, we generate file in ANSI. Is there a setting we need to change to generate files in UNICODE? or how can we convert ANSI files to UNICODE?

SELECT MAX pulls too many rows

Posted: 05 Apr 2013 05:10 AM PDT

I know you guys are tired of hearing this and seeing this same question asked over and over (figured this out from reading your forums for days trying to find the answer). I'll apologize up front because I am new to SQL, having been thrown in during the middle of implementation of new software. But I AM trying to learn, just having to do so a lot faster than I'd ever have planned for myself !:w00t:I have written the following query and would like for the results to give me ALL the rows that match my MAX Batch ID, but I am getting all the rows for all of the batches that belong to that customer.Declare @actid VARCHAR(10) SET @actid ='642584789T'SELECT d.cust.id, rtrim(isnull(d.cust_ln,''))+', '+rtrim(isnull(d.cust_fn,'')) AS Name,convert(char(10),d.ord_dt,101) as OrderDate, d.item_name, d.item_qty, d.rowno, d.bat_num, (SELECT MAX(d.bat_num) FROM ORDERS.dbo.tb_batch AS d WHERE d.bat_num=e.bat_num) AS Batch FROM ORDERS.dbo.tb_batch AS d left join ORDERS.dbo.tb._cust AS e ON d.bat_num=e.bat_num and d.rowno=e.rowno WHERE d.acct_id = @actid THE RESULTS I GET ARE: Cust_id. . .Name. . . . .OrderDate. . . .item_name. . . .item_Qty. .rowno. . Batch 1. . . .Smith, Ed. . . .03/01/2013. . . Broom #3. . . . . . . . 4 . . . . .647. . . 2582 2. . . .Smith, Ed. . . .02/01/2013. . . Shovel #4. . . . . . . .6 . . . . .647. . . 2582 3. . . .Smith, Ed. . . .03/01/2013. . . Rake #2.. . . . .. . . 4 . . . . .256. . . 1645 4. . . .Smith, Ed. . . .02/01/2013. . . Shovel #4. . . . . . . .6 . . . . .256. . . 1645 I would like to see only the two rows for the MAX batch ID of 2582.

Possible to alter a server-side trace without deleting and re-creating?

Posted: 05 Apr 2013 03:01 AM PDT

On the servers I'm now responsible for, the previous DBA implented a server-side trace. This trace logs quite a bit, allows the files on disk to grow to 1GB, and keeps 5000(!) files.Well, the servers don't have 5TB drives (I wonder if they had intended to have 1MB files) so I've been having to go in to delete old files to keep them from filling up whatever drive they're getting saved to.What I'd like to do is, alter the trace to either keep the files to 1MB (maybe 10MB), or keep (a LOT) fewer files (maybe only 50 or so)I've seen all sorts of articles on altering a trace to capture different data, or stop capturing some data, but nothing to indicate that I can alter the filesize or filecount.Can I do this? Or will I need to stop the trace, delete the trace, and re-create the trace with the new values?Thankfully, the previous DBA saved the trace creation script, so re-creating won't be too big a hassle, if that's the solution.Thanks,Jason

How to copy some objects (tables, views, store procedures) from one DB to another DB?

Posted: 05 Apr 2013 04:56 AM PDT

How to copy selected objects (tables(including data), views, store procedures) from one DB to another DB?

?? on set parameter to today if date in future?

Posted: 05 Apr 2013 02:58 AM PDT

HiDoes anyone know how I can setup this where statement to have @enddate be now() if the date is in the future?(calendarDate BETWEEN @startdate AND @enddate)ThanksJoe

Possible bug in Microsoft system stored procedure

Posted: 05 Apr 2013 01:26 AM PDT

Our application runs into an issue when executing sys.sp_fulltext_catalogs, it throws an error stating that "System.Data.SqlClient.SqlException: Arithmetic overflow error for data type smallint, value = 235139." as I traced it out, the error occurs at line 56where @ftcatid variable being declared as smallint, while the fulltext_catalog_id is defined as integer in the fulltext catalogs table[code="sql"]declare @ftcatid smallintselect @ftcatid = fulltext_catalog_id from sys.fulltext_catalogs where name = @ftcat[/code]Could some experts take a quick look and confirm?-Thanks

replication question

Posted: 05 Apr 2013 02:23 AM PDT

Greetings,We are moving from our current sql server 2008 R2 to newer sql server 2008 R2.We have about 10 databases and 1 of it is used for querying data for external customers. The external customers login and execute a procedure and read whatever data they want to copy . Every time it is done a record is added in the database for audit purposes. As we move into newer server the external customers should be given time to point to the new DNS name of the sql server. During this time the 5 tables in the database should be in sync between the current and new sql server databases. What is the right approach to keep the databases in sync (replication, mirrioring, etc.,). We use standard edition sql server 2008 R2 box on both the servers.Thanks

Linked Server to Advantage DB using ODBC

Posted: 05 Apr 2013 02:17 AM PDT

Hi GuysI have a bit of a problem. My linked server test returns successful, but when I try and run:select *from openquery(bradford,'select top 1 id from ttfa0s')I get the following:Msg 7399, Level 16, State 1, Line 1The OLE DB provider "MSDASQL" for linked server "bradford" reported an error. The provider reported an unexpected catastrophic failure.Msg 7330, Level 16, State 2, Line 1Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "bradford".I can run the same query, or anything I want in MS Access using the same ODBC connection, so I can't see it being a hardware issue...Can anyone see what I'm doing wrong? Thanks in advance...

How to get result sets when using dynamic query

Posted: 05 Apr 2013 01:04 AM PDT

This procedure returns ID and Question ID . I previously was sending data at application level by appending them in varchar variable but in dynamic query m unable to do it. I tried to get the resultset at application level by ISingleResult as i have connected application through Linq to SQL but it is also not working please help . CREATE PROCEDURE [dbo].[Get_Ques_id_for_can] (@candidate_id varchar(max),@Exam_id varchar(max),@sec_id bigint,@Q_id varchar(max) OUTPUT)ASBEGINdeclare @table_query varchar(max)declare @table varchar(max)set @table=@candidate_ID+@Exam_iddeclare @id varchar(50)declare @ques_id varchar(50)set @table_query='SELECT [ID], [Question_ID] FROM ['+@table+'] WHERE [Section_ID]='+CONVERT(varchar(50),@sec_id)EXEC(@table_query)END

Use of Threads in Data Flow task of SSIS

Posted: 05 Apr 2013 01:07 AM PDT

How are the threads used by data flow engine?Is it like it assigns same no of threads per data source and equivalent worker threads?If I have set a max row size or 1000 and have 1M rows in source. There is one transformation and finally one destination.How the data flow engine manages the data in the above scenario?

Why is RPC duration dependent on which server making the call?

Posted: 20 Mar 2013 09:45 PM PDT

I have a problem which I've tried to solve for several days now.The customer was complaining that the system is slow. I ran SQL Server Profiler and saw that e g a stored procedure (RPC:Completed event class) runs 10 to 50 times slower than normal, but still with the same number of writes (about 400).Having ruled out a lot of different hypothesis about the cause of this problem I landed in the fact that:[center]Depending on which web server making the call to the db server, the stored procedure performs differently![/center]I have the same binaries installed on each web server, so that should not be the problem. All remote calls from the different servers goes fast, except when the production server makes the call. Then the duration of the stored procedure is 10 to 50 times longer than for calls from the other servers.The problem started with no connection to any known changes of the system, so that adds to the mystery, but the main mystery I want solved is the question above, namely why is the execution time for the stored procedures at the db server dependent on which web server calling??Any ideas?

MsxEncryptChannelOptions and Multi-server administration

Posted: 01 Mar 2013 01:30 AM PST

Setting up the multi-server administration failed for me because the MsxEncryptChannelOptions registry value on the server I am attempting to make the target is set to '2'. According to Microsoft and everyone else on the interwebz, changing it to '0' resolves this issue. My question is what are the effects of changing the value to '0'? Seeing as this is a production server, making changes to the registry without knowing what else it will effect kinda makes me nervous. What else uses this registry entry or is it exclusively used by the multi-server admin functions?

#Error is SSRS 2008, for null values

Posted: 04 Apr 2013 08:54 PM PDT

Hi, My datasource is a cube, and the dataset has a field MTD, which is returning NULL, and when i try to handle it with 1. iif(isnothing(Fields!MTD.Value),0,Fields!MTD.Value) , I get #error in the report 2. iif((Fields!MTD.Value).Ismissing,0,Fields!MTD.Value) , I get #error in the report 3. iif(Cint(Fields!MTD.Value),0,Fields!MTD.Value), I get #error in the report 4. I even wrote a funtion to pass this parameter and return 0 if missing, but still I m getting # error in the report I m using Microsoft VS 2008, Version 3.5 SP1 Please let me know any possible fixes as soon as possible Thanks Srividya
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog