Tuesday, April 16, 2013

[SQL Server 2008 issues] Changing Execution Plans in Cache

[SQL Server 2008 issues] Changing Execution Plans in Cache

Changing Execution Plans in Cache

Posted: 15 Apr 2013 07:47 PM PDT

Hello,I've exerpeinced something I've not noticed before, We have a heavily stored procedure in on of our databases and we've seen intermitting perfromance issues over the last week and it looks like the execution plan changes even through the stored procedure remains in cache. We've fixed the issue my using the forceseek index hint.What would cause a plan to be changed? In our case an index seek on a very large table changed to scan and caused the procs performance to dive big time. Dave

Is there a better way to insert data than Access?

Posted: 14 Apr 2013 10:41 PM PDT

Is there a better tool to insert data into SQLserver than Access ?For managers. Small number of people. Very limited amount of data. For small administrations. To replace spreadsheets used by the managers.(Insert, read at and modify data).Ben BrugmanLast week, I tried to ask the same question but bumbled.

Reinitialize log shipping in sql server 2008 R2 after activate Secondary server DB

Posted: 15 Apr 2013 02:24 AM PDT

Hi All, I have set up logshipping between server A (Primary) and server B (Secondary), and tested log shipping by activate database from server B ( Stop the log shipping job and Restore database with Recovery on server B) . Now i wanted continue the log shiping from this break point . Can continue without restore the full backup from server A ? Please helpRegardsSuresh

Give access to SQL Server Agent ONLY?

Posted: 15 Apr 2013 05:15 PM PDT

Just wondering if it is possible to give someone SSMS access, however only allow them to run jobs within the SQL Server Agent and nothing else?

Ranking Query

Posted: 15 Apr 2013 09:53 AM PDT

Hi All,i have a requirement to find out which employee pass three exams continuously if you observe below query dataset a passed three exams continuously again b also passedc and d failed again e passedfinally it should display a,b,e rows only declare @table table(id int ,name varchar(20),row int identity(1,1) )insert into @table select 1,'a' union allselect 2,'a' union allselect 3,'a' union allselect 1,'b' union allselect 1,'b' union allselect 2,'b' union allselect 3,'b' union allselect 3,'c' union allselect 3,'c' union allselect 3,'c' union allselect 6,'d' union allselect 6,'d' union allselect 7,'d' union allselect 9,'e' union allselect 10,'e' union allselect 11,'e' select * from @table here i am trying to fetch by using ranking functions but not able to find out the query please help any oneRegards,Jagadeesh

Comparing Two Different Tables, Showing Values In Table 1 Not in Table 2

Posted: 15 Apr 2013 12:23 PM PDT

Hello,I'd like to compare two tables, and show values found in table 1 which are [b]NOT[/b] in table 2, then same for table 2, show values in table 2 which are [b]NOT[/b] in table 1.Here's what I came up with, looks ok?select T1.[Name], T2.[Address]from [dbo].[My Table 1] as T1left join [dbo].[My Table 2] as T2 on T1.[Name]=T2.[Name]where T2.[Address] is not nullThanks

No of rows in Temporary Table

Posted: 15 Apr 2013 04:54 PM PDT

What is the maximum number of rows can be stored in a emporary table?

Fixing Divide by Zero Error

Posted: 15 Apr 2013 03:52 PM PDT

Hello,I have in select statement select t1.A/t1.B but get divide by zero errorwhen I used with isnull isnull(t1.A/t1.B,'-')I get error Error converting data type varchar to float.how to fix?Thanks

Large dataset in one table or several small tables

Posted: 15 Apr 2013 10:48 AM PDT

Hello.Here is my question: Is it better to store a "large" set of data in one table or break it across smaller tables, each with the same schema?Here is the background: In my client's words there are currently "millions-soon-to-be-billions of records", and they are expecting "thousands of read and write hits per day". The data table is flat, has 33 columns total, and an expected row size of 510 bytes (based on directions at "http://msdn.microsoft.com/en-us/library/ms189124.aspx"). 5 of the 33 columns are used to identify a particular 'real-world entity' in the table. (this is where my vocabulary may trip me up - hopefully i am using 'Entity' correctly). A single real-world entity will likely have more than one row of data associated with it (imagine a single garden plot with many 'crops' planted in the garden. The 'entity' is the garden plot; the data are the various crops planted in the garden). The schema for the 5 columns used to identify an 'entity' is listed below (I left out the remaining 28 columns for brevity. I can include them if necessary). The remaining fields are predominantly varchar with a couple of ints and dates in the mix as well.CREATE TABLE [dbo].[TestTable]( [field1] [varchar](10) NOT NULL, [field2] [varchar](7) NOT NULL, [field3] [varchar](7) NOT NULL, [field4] [varchar](10) NOT NULL, [field5] [date] NOT NULL)I have recommended that the client keep the data in a single table, indexed based on the 5 identifying columns. The data are specific to a US State/County combination so the client is learning towards a "per state" approach, i.e. one table for each of the 50 states, or (really crazy i think) a "per-county" approach with 3000+ tables, one for each US county. The business-logic that has me concerned, or in doubt, about my recommendation is that when a single row for a given real-world entity is updated, all rows associated with that entity are deleted, and the complete set of data for the given 'real world entity' is inserted. My concern is that potentially 'thousands of writes' per day would negatively impact the table's index. But i am figuring that regular rebuilds of the index done as part of a maintenance plan would take care of the fragmentation issues (if that is even an issue).What do you all think - one 'large' table or several/many smaller tables?Thank you

Turn off Predictive Typing

Posted: 11 Nov 2010 07:09 PM PST

Hi,There does anyone know how to turn of the Predictive Typing feature in SQL Server 2008 and 2008 R2 when typing in a query analyser windowThis has to be the worst feature i have ever seen, it now takes longer to type a piece of SQL as i keep having to correct what it assumed i wanted to type.Thanks in advance for any helpCheers,Brunzire

Copy a table from one db to another including all configurations

Posted: 11 Apr 2013 01:22 AM PDT

How to copy a table from one db to another including all configurations, such as index, primary key...I use select * into newtable from originaltablebut missing indexes.

is there a way to skip or ignore unwanted files?

Posted: 15 Apr 2013 06:30 AM PDT

I want to skip or ignore those files that have a "unmatching" pattern in them (i.e, a_unmatched_b.xlsx, b_c_unmatched.xlsx, and so on). Is it possible to do this in SSIS?

Procedure Execution Plans

Posted: 14 Apr 2013 11:07 PM PDT

Hi all,I have a question regarding the best way to approach a situation where an application user can query a set of tables in two different ways. Say there is a "View Mode" that indicates whether the user wants to search by Location or by Property ID range. The underlying Select statements would differ only in the WHERE clause, the table joins would be the same. If two PROCS (B and C) are used they would have different parameter lists and WHERE clauses.) It seems there are these choices:1. PROC A would include View Mode and some optional parameters and would say: If ViewMode = 1 EXEC Proc B Else EXEC Proc C;2: PROC A would include View Mode and some optional parameters and then branch to one of two different SELECT statements within the PROC. It would not use Procs B and C at all.Am I correct in thinking that the execution plan for choice 2 would be inefficient at least part of the time? Would choice 1 be any better? We'll probably end up just letting the app code decide which proc (B or C) to call but I'm still curious about the general scenario.Thanks

Viewing SSRS pie chart report on mobile devices

Posted: 15 Apr 2013 06:52 AM PDT

I have an SSRS report that includes tables with data in them and also a pie chart with percentages. This report is currently emailed as a pdf attachment. Due to request of the data it needed to be viewed when not at a computer. So I changed the report to email the entire report in the body of the email as opposed to as an attachment. In viewing this emailed report thru any mobile device the pie chart is not viewable or readable depending on the mobile device. Does anyone know 1) are SSRS reports containing a pie chart only to be viewed on a computer 2) if a report does contain a pie chart how to make it presentable to view by any mobile device's email?

RFI: what do you do during production instances?

Posted: 15 Apr 2013 05:25 AM PDT

The CEO of our company wants to know what all the different departments within IT (server operations, network operations, DBAs, etc.) do during a "production incident" to help diagnose the problem. As one of the DBAs, I've been asked to work on the database part of this request.Realizing that this is a very wide and broad subject, what tools do you use, actions do you perform, etc. when "production incidents" occur in your environments? Some things I've identified, in no particular order, include: Check disk space and database file spaceExecute sp_who2 or a derivative thereof to see what other SQL processes / jobs may be running, causing blocking, etc.Check Task Manager to see what other applications, services, etc. are running on the server, consumption of CPU and memory resourcesCheck SQL and Windows error logsMy plan is to develop / plagiarize a script or series of scripts that I can "pull the trigger on" at the beginning of an incident to gather all of the above information and anything else that I'm overlooking, get a one-stop place to evaluate the results and make a decision on how to proceed.The majority of the SQL Server instances in our environment are SQL Server 2008 R2, of various editions, with a very few SQL 2005 instances hanging on, and just getting started in SQL Server 2012. Do any of you have suggestions on additional areas to focus? Scripts that you use? Blogs that have been read / written that I can refer to?Thanks in advance for any suggestions, advice, etc.Mike

Update query help

Posted: 15 Apr 2013 12:36 AM PDT

Hi,I need an update query which will get the 'scenario' and 'actType' values from all the rows in column strComment and insert the values of 'scenario' into column keyScenario and the value of 'actType' into column act_Act.Please look at the picture attached.Thank you in advance.

Exceeding 25 replication agents on an instance

Posted: 15 Apr 2013 02:37 AM PDT

Using SQL Standard Edition (2005, 2008) the server is not able to startup more than 25 replication agents.We've run into this using SQL 2000 and had to place a call to MS. There is an undocumented registry setting that will allow more than 25 replication agents to be configured per instance.Unfortunately it's been ten years since we've run into this problem and are unable to find any documentation on the setting. Anyone here familiar with the problem and have a solution?

Using linked server or create another connection string?

Posted: 15 Apr 2013 02:09 AM PDT

In my one asp.net app, I created a connection string to ServerA and then created a linked server from ServerA to ServerB. It working fine.But, new boss said that linked server is not good practice. App should void using linked server and should create another connection string to ServerB.Is it true?

Sqls2k8 R2 maximum database size

Posted: 15 Apr 2013 01:01 AM PDT

I have a data about 250Gb to 400gb.. And now i have doubt about which database i have use to store this data?

Cross Server Dictionary

Posted: 14 Apr 2013 10:55 PM PDT

I guess this post is not a 'question' per se, but more just me rambling on about my approach so far, and see what feedback comes in return.Maybe I've overlooked some points, maybe (likely) there's a better solution/approach out there....?I'm just seeking the opportunity to bounce my world against people and see what they think.BackgroundSo, I am tasked to document our databases.The idea being that whether you are a developer, reporter or occasional user.... you are not just victim to interpreting an ambiguous table/field name, but can access a wide range of support information.The new auxiliary tables would in simplest terms contain textual descriptions, links to supporting documentation outside of database.In addition, there would be a hierarchy to group field of equal and similar significance (departure location, arrival location, manufacturing location .. would all roll up to an entity called 'location')Phase 1 was just to prototype a dictionary with 1 database, but now Phase 2 is to create 1 single dictionary to contain the meta-data of multiple databases residing on different servers.In an ideal world, I'd like to have the best use of existing system tables as possible for the foundation level, after all, why duplicate something that already exists and if maintained for you automatically ?However, I have struggled with some points of merging and utilizing system data form multiple databases/servers... and I am beginning to re-invent a new system table structure.Thus I'm baring my progress so far in the hope that someone can help re focus how I can make all of this more streamline !Approach (so far!)As we know, we can find all sorts of information about tables, fields, relationships from various system tables and information_schema views.Yet my first challenge was how to link the SQL Server system data, to my auxiliary data.Linking full textual tablename/fieldname would be just inefficient; ID fields would be needed.In an ideal world, I could just use sys.objects object_id, with an additional field to differentiate severs.However, just utilizing the system object_id's might cause problems down the line, as these may change in the case of objects being modified/dropped/recreated. This would cause my subsequent hierarchies to fall apart.In the end I decided that I would require both a permanent non-system ID that would identify a unique field/table for my aux data, and also a procedure to assign to that row, the current system object_id. (weekly job to synchronize based on catalog/schema/table/field name).Hence I will maintain a permanent auxiliary structure with my own Identities, and be able to reference system tables with the normal object_id.My new table now looks something like this :[img]http://www.netshed.co.uk/temp/T1.JPG[/img]For Phase 2, we have to accommodate objects from multiple databases.[img]http://www.netshed.co.uk/temp/T2.JPG[/img]So that's where I am at right now.Feel free to rip it all apart and show me a simpler way to create a dictionary that will bridge databases and servers !

Is there a better way to insert data than Access?

Posted: 14 Apr 2013 10:41 PM PDT

Is there a better tool to insert data than Access ?

Need Link

Posted: 14 Apr 2013 09:28 PM PDT

Can anyone provide link for :Visual Studio 2005 as i am unable to find it

Views + OUTER APPLY

Posted: 14 Apr 2013 09:13 PM PDT

Hey,SQL Views don't seem to like OUTER APPLY, so is there an alternative (I know they work, just not in Diagrams etc.).This is what I need to achieve;[code="sql"]OUTER APPLY (SELECT TOP 1 acr.ACR_ACT_Code FROM [Activity Records] acr WHERE acr.ACR_Clock_No = cr.CLK_Clock_No AND CONVERT(date, acr.ACR_Shift_Date) = CONVERT(date, cr.CLK_Shift_Date) ORDER BY acr.ACR_Start_Date + acr.ACR_Start_Time DESC) acr [/code]Thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog