Monday, March 4, 2013

[SQL Server 2008 issues] confused With View , Function and Store Procedure

[SQL Server 2008 issues] confused With View , Function and Store Procedure

confused With View , Function and Store Procedure

Posted: 03 Mar 2013 06:08 PM PST

i get confused to where i use function , store procedure and view.what is the definition and deference of them?;-);-):hehe:

Identifying performance issues using Profiler trace

Posted: 03 Mar 2013 06:00 PM PST

Hi All,I have been told that I would be given a production profiler trace to figure out the performance issues during application peak time. Assuming required properties are chosen while producing trace & SqlServer 2008R2 is the version. I am aware of some tuning methologies if I identify the bad performing query (adding indexes, re-write logic). However, I need to start from trace and narrow down to bad performing queries. please help me with some formal way how to progress once I receive the Trace.Any steps/docs would help me to kick start pls.Thank you all.Regards,Suresh

Just accepted a Sr. DBA job. Time for a gut check?

Posted: 03 Mar 2013 12:46 PM PST

Hi SQLSC!Buckle up, I apologize in advance for the long post. In many ways, typing it out is as much as my own sanity as it is for me asking for everyone's advice. I hope you take the time to read it and comment; I am need of advice.First off, I'm a new poster in the forums, but not to the forums or SQL Server central itself; I've been a lurker here for some time, and I have to say that I am routinely impressed with the knowledge level, maturity, and overall helpful attitude of all the members that ask questions and provide solutions here. Which is why I'm here, because I need some advice from the people who know the most about my "dilemma."I recently interviewed for, was offered, and accepted a senior database administrator role. And now I'm having a form of "buyer's remorse" so to speak, for two main reasons:1. I've been at my current employer for almost 10 years, and2. I am wondering if my skills are where they need to be for this role.Aside from the fact that I'm leaving a relatively stable and secure job for a new role at a new company where I'll be "the new guy" (which is stressful enough), I have a terrible fear of failure; I know that's a "me problem" and it's not confined to my job. I feel this way about a lot of things, and truthfully I should probably be medicated :w00t:Let me provide a little background:I started my career as an "accidental DBA" back in 2002. At the time, I was tasked with creating a local web page for people to post articles, announcements, and so forth for everyone in the building to see. I went to school to be a software developer, and I go swept up in the web application development craze while there. I started out coding pages in PHP with a MySQL back-end, and graduated to active server pages with Microsoft Access databases that I "inherited" when other people left the company. In 2004 I obtained my first SQL Server instance: a SQL server 2000 installation that I ended up migrating all my legacy application databases to.I had never really used a full DBMS before, so naturally I started out slow, learning the system and how it worked. I built tables and views, and I even messed around with DTS packages to automate some of my tasks. I learned how the transaction logs work (the hard way, when thew grew out of control) and how to manage them. I learned the value of backing up databases, and the value developing a solid disaster recovery plan (and making sure people knew how to execute it when I was not around).From there, I made the switch to ASP.NET, coding in C#. I recoded lots of applications, and around 2007 I took the opportunity to migrate to a brand new instance of SQL Server 2005. I started coding TSQL stored procedures and functions, and started the best practice of using them for the actual business logic and SQL code instead of embedding it in my applications. I got to work with SSIS, which I love, and continue to use for many things. And up until recently, I wrote many complex stored procedures with CTE's, temp variables and table variables, and the like. I've also followed all the best practices I've can in regards to "least required" access rights, creating application log ins, restricting users to certain schemas based on business needs, and even using "EXECUTE AS" when needed, both with local SQL server log ins and domain ID's. And today, I manage 3 severs (2 virtual 2008 instances and 1 physical 2005 instance) that my applications all use to store, read, and execute on data.So why leave? Well in my current role, I'm not really considered to be in "IT." I'm in a position where I'm part of the operations of the business because of my diverse skills; I get to work with IT, though, in securing new instances of SQL server hardware, VM's, or other developers in getting access to other systems and/or providing access to my own database servers. In many ways, I feel my development background gives me a proclivity to working with TSQL and developing complex queries. Coupled with the fact that I've been writing SQL for almost 10 years now, I feel I have a pretty solid handle on what makes good, quick-executing queries and what doesn't. And I love SQL server; working with data sets is something I truly enjoy. In many ways, I feel my development background gives me a proclivity to working with TSQL and developing complex queries. Coupled with the fact that I've been writing SQL for almost 10 years now, I feel I have a pretty solid handle on what makes good, quick-executing queries and what doesn't. And I love SQL server; working with data sets is something I truly enjoy.But I found myself at a cross-roads; while I'm viewed as a "go-to" person for solutions, I felt I've hit a ceiling. Since I wasn't IT, I knew I wasn't going to be able to learn, say, SQL Server 2012, or be able to attend classes for certification or training; it's just not in the budget for the area of the company I worked for. And in some ways, my job could clearly be viewed as "redundant" since it's an IT-based role in the non-IT division. It's been tried before, and there were many times I was told (in confidence) that my job had to be justified at the highest levels. Which is always good to hear...At any rate, I started looking. I felt I had two career paths: to continue as a developer, or to look for database administrator jobs. Again, being self taught, I felt my development skills are "behind the curve" a bit (seriously, does anyone want web forms anymore? Everything is MVC now), I decided to look for DBA roles. It didn't start out good; the first job I interviewed for (which was an ETL-type role) I interviewed sat me right down and had had me do some "live" testing by developing some SSIS packages in front of some of the other staff. I was a little flustered, and while I was able to complete the tasks, I think they felt I was a little under-prepared and passed on me. The second job I interviewed for was even worse; I never got to talk to the hiring manager. HR brought me in, and sat me in front of a computer terminal with some testing software. I bombed horribly, and was told that my skills were sub-par.So was starting to feel defeated; I studied up, and practiced at my current job, learning as much as I could. So when I went to my third interview, I wanted to be better prepared. But when I got to my third interview, there was no testing at all; First, there was a phone interview where they asked me some questions about what I did now. I told them basically my story above: that I was an application developer that had to work with SQL server. They wanted to hear more, so I gave them all the answers I could. They asked some questions that I felt I didn't have good answers to, and I told them so. The interview lasted a half an hour, and I hung up the phone thinking "that went well, but there are probably better qualified people."Imagine my surprise when, almost 10 minutes later, my phone rang. I was my recruiter; he told me that the hiring manager and current DBA were "blown away" with my experience and wanted me to come in for an interview asap. He told me that many people didn't even make it past the phone interview! So I was psyched, but nervous: what if I bombed another test? I shared my worry wit my recruiter; he told me just to do my best.I showed up to the in-person, and first met with the hiring manager in person. We talked for almost an hour, but it was more about the kind of person I was and what I liked. Why did I want the job? In effect, he was measuring up my personality, which, as a person who had hired some analysts in the past, I appreciated. From there, I met with the members of his team: server administrators, storage administrators, the security team, active directory administrators, exchange team... the meetings went great. I got to spend some time with the current DBA (who, I should mention, has been there two years and I am going to be working WITH, not replacing). Finally, I met with members of the development staff. This was the most technically demanding part of the interview; they grilled me on some SQL stuff ("Would you ever use a cursor?" "What's a left join?"), and and some application development stuff ("Ever worked with Hadoop?") and also about my current release management. I gave the best answers I could.When we were finished, I glanced at my watch, I was there about 3 hours! I was escorted back down to HR where I met with the HR senior manager. She talked more about the company and the benefits, and then she told me that she wanted to make me an offer right then and there. I was floored. I asked her if maybe she should get some feedback from the team to make sure, to which she replied "I wouldn't be making this offer if every single person you met with didn't come down and say 'hire this guy.'"I had a few days to think about it, and I decided to accept; the company seems great, it's growing, and the culture is very appealing. I'll have access to a formal training and certification budget to keep my skills sharp, and I get to get back to an pure IT role.Sounds great, right?Well, back to my dilemma: since accepting, I am having a pure panic attack. Do I really have the skills to do this? There are certain things in the environment that will be new to me, such as:1. The scale: there will be many servers to manage, much more than I've had to work with in the past.2. The size of the instances: While the company is MUCH smaller than where I was, they have massive amounts of data. My tables maybe held 10's of thousands of rows, but some of their production databases hold 100's of thousands of rows.3. They utilize a HA set up on a cluster; I don't have any experience with those.4. They have replication set up on a few of the production databases to create an instance for the BI and operations teams to query; I've never set up or changed replication schemes.Also, I've always been the person who's had to develop the application code as well as the TSQL code to support it, I've never really had to troubleshoot major performance issues; that's not to say that I don't understand query execution plans, but I haven't had to use database tuning adviser, or SQL Server profiler to really dig into a query issue. If something wasn't working I could pretty easily identify where an index was needed or a procedure that needed re-written. So I don't have a firm grasp on what the "best" way to troubleshoot performance issues are.But part of the reason they liked me for this role was because of my development experience; the current DBA is bombarded with questions for the various development teams asking him for help with their queries. The fact that I have development experience is why they selected me: to help ease the burden on the current DBA, and to help suggest different ways of accomplishing tasks to the teams. They are undergoing an massive overhaul of many of their systems, and trying to position them company's information systems for future growth.I'm not exaggerating when I say I haven't been sleeping well; I feel like I don't have all the skills I need for this job, and that I'll fail and regret giving up what I had. Ever since accepting, I've been reading all my books, re-visiting the stairwell articles here, and reading and testing myself to make sure I'm ready. I start the job in a couple weeks, and I want to make sure I'm ready. My absolute low point came last night: I had recently bought the test prep books from Microsoft press for two of the certifications. I read the books and studied a bit, and I took the practice tests: I failed both of them. Cue panic.If you've read this far, I want to ask for all of your advice: do you think I accepted this role too hastily? Am I missing critical job skills? Are there areas that you would suggest me boning up on? I had an opportunity to speak to the current DBA, and he doesn't seem concerned about my skill set, but I don't want to be another person who burdens him with questions when I don't understand something. Or am I over-thinking it? Should I just have faith in my abilities and move forward? It all boils down to: I want to come in and be successful. I've been good at my job in the past and this will be a change for me. In six months (or less!) I don't want them to think they made a mistake in hiring me. I really feel the company I going to is a good one, with a very bright future, and that the role is a positive step for my career.Having typed all that out, what do you all think? Did you go through something similar? Any advice or comments are welcome, and I thank you all for your time and thought. I'll certainly be monitoring the thread, so if there's other things that you want to know about me or my skills, feel free to ask!

Use parameter value in .csv output filename

Posted: 03 Mar 2013 05:02 AM PST

Table_1[code="sql"]ESTAB_ID ESTAB_CODE FORENAME SURNAME1 9009001 John Jones1 9009001 Mike Smith1 9009001 Mary Yates2 9009005 Ann Ennis2 9009005 Kelly Homes3 9009014 Harry Brand3 9009014 James Casey3 9009014 Chris Balls[/code]Table_1 contains a list of people belonging to different establishments. The ESTAB_ID is a unique sequential identifier. The ESTAB_CODE is an integer value and not sequential. I have written the following to extract subsets of the table (by ESTAB_CODE) and output them as .csv files.[code="sql"]DECLARE @ESTAB_CODE VARCHAR (7)SET @ESTAB_CODE = (SELECT ESTAB_CODE FROM TABLE_1 WHERE ESTAB_ID = 1 GROUP BY ESTAB_CODE)SELECT [ESTAB_CODE],[first_name],[last_name]FROM TABLE_1WHERE ESTAB_CODE = @ESTAB_CODE:OUT C:\FOLDER\9009001.csv[/code]I set SQLCMD mode to 'ON'.I manually change the ESTAB_ID number in the SET line to produce the different subset tables - I am happy to do that at this stage.I get the correct output in the correct destination folder in .csv format.[code="sql"]ESTAB_CODE FORENAME SURNAME9009001 John Jones9009001 Mike Smith9009001 Mary Yates[/code]My problem is the filename for the .csv file. I want to use the ESTAB_CODE number for the filename and have to imput it manually. Is there any way this filename can be changed automatically to the current ESTAB_CODE using the @ESTAB_CODE parameter value when I execute the code?I am very new to SQL having spent many years in a SAS environment so tend to expect SQL to work in a similar way - which is causing me problems!!Any assistance would be much appreciated.

Query in SSRS

Posted: 27 Feb 2013 07:51 PM PST

hi All ,i have one requirement where the SSRS report is displaying only certain data of a column based on a filter on a date range ,whereas i should get all the data even though the date range is selected or not for that particular column ,how can we achieve this is SSRS report

how to get Mac Address in SSRS

Posted: 27 Feb 2013 02:45 PM PST

Hi,I am having a requirement of taking the MAC Address using SSRS is it is possible to take?My Requirement: To take and keep the mac address of each user and make a note that he does not make double entry of comments ,there is no log restriction like using userid and password , just to access the page and user will give some comments.Other than MAC Address can we do this?If so how to find the uniqueness on the users.Notes:A user should not have double entry.No user is provided Userid and password.Thanks!

Use parameter value in .csv output filename

Posted: 03 Mar 2013 04:32 AM PST

Hit the send button in error. see my later post with the same title

fetch tables most often queried

Posted: 26 Feb 2013 12:24 PM PST

Is there a way to determine which tables of a database are being queried most often, sorted in descending order?

Not seeing savings in sparse columns

Posted: 02 Mar 2013 10:23 PM PST

Ive ran a test comparing a table with a few sparse columns to a table with no sparse columns, and i am seeing zero space saving.I have two tables, both storing Address info mainly in varchar columns. both tables allow nulls, one has columns sparse property set.I insert 1000 rows of default values in each (default values are null). Sparse columns store nulls differently so i believe i should see a space saving. but on running sp_spaceUsed i dont see any saving. Any ideas on what i am doing wrong or where my understanding is incorrect?[code="sql"]Create Table SparseColTest_NonSparse( AddressID int identity(1,1) not null, AddressLine1 varchar(500) null, AddressLine2 varchar(500) null, AddressLine3 varchar(500) null, PostalCode varchar(20) null, Country varchar(50) )Create Table SparseColTest_Sparse( AddressID int identity(1,1) not null, AddressLine1 varchar(500) sparse null, AddressLine2 varchar(500) sparse null, AddressLine3 varchar(500) sparse null, PostalCode varchar(20) sparse null, Country varchar(50) )declare @i intset @i = 0while(@i <= 100000)BEGIN insert into SparseColTest_NonSparse Default values insert into SparseColTest_Sparse default values set @i = @i + 1ENDexec sp_spaceUsed 'SparseColTest_NonSparse'exec sp_spaceUsed 'SparseColTest_Sparse'/*name rows reserved data index_size unused----------------------------- -------------------- ------------------ ------------------ ------------------ ------------------SparseColTest_NonSparse 210003 2888 KB 2840 KB 8 KB 40 KBname rows reserved data index_size unused----------------------------- -------------------- ------------------ ------------------ ------------------ ------------------SparseColTest_Sparse 210003 2888 KB 2840 KB 8 KB 40 KB****NOTE - even with 210k rows sparse and non sparse tables are identical in size.*/[/code]

partition_id VS. hobt_id

Posted: 02 Mar 2013 09:44 PM PST

Hello!There is 2 columns in [url=http://msdn.microsoft.com/en-us/library/ms175012.aspx]sys.partitions[/url] view - [b]partition_id[/b] and [b]hobt_id[/b]. From my point of view/experience for any given row from this view [b]partition_id[/b] always equal [b]hobt_id[/b] and I never see the 2 different values. It's seems reasonable, because (simplifying complex thing) we can say: "partition is hobt, hobt is partition". But at the same time article about another view - [url=http://msdn.microsoft.com/en-us/library/ms189792(v=sql.105).aspx]sys.allocation_units[/url] tell us:[quote]If type = 1 or 3, container_id = [b]sys.partitions.hobt_id[/b].If type is 2, then container_id = [b]sys.partitions.partition_id[/b].[/quote]So - these 2 values can ever be NOT equal?? As I said I had never see this in practical. Can you please give the example of table (and DB, if it needs), for which [b]partition_id[/b] and [b]hobt_id[/b] will be NOT equal?
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog