Three main methods are used for removing caffeine: chemical solvents, liquid carbon dioxide (CO₂), or plain water with special filters. The additional steps required in all of these processing methods are why decaf coffee is often more expensive.

How is decaf coffee made? And is it really caffeine-free?

In a birthday celebration post, the Barefoot Contessa’s husband, Jeffrey, shows us how to make a great cup of coffee.

How to Make a Perfect Cup of Coffee, According to Ina Garten’s Husband

Have the chocolate candy gremlins picked your Halloween stash clean? Here’s a vodka-infused idea for the leftover sugary stuff.

Drink the rainbow. Skittles vodka slushies hack into that Halloween stash

Unlike other types of coffee, you don’t filter out the grinds in Turkish coffee. The grinds remain in the coffee and often settle at the bottom of the cup. There’s even a long-standing tradition of fortunetelling using the grinds left over after the ...

At Penny's, watching Turkish coffee brew is as delightful as drinking it

Who knows? If you catch your barista on a good day, you may even get a little extra free coffee. To try this tip, open up the Starbucks app. When you order your usual filter coffee, hit the customize button and scroll down to cup options, where you'll be ...

The Starbucks Cup Hack That Ensures You Get More Bang For Your Buck

Our acting head of e-commerce reviews the Cuisinart Grind & brew single-serve coffee maker in the Future test kitchen

Cuisinart grind & brew single-serve coffee maker review

Amatera has closed a €1.5M pre-seed funding round to speed up production of its climate-resilient perennial coffee crop varieties.

Climate-Resilient Coffee: Meet Amatera, The French Startup That Raised €1.5M to Accelerate Its Perennial Crop Tech

One of the best Black Friday deals for coffee lovers is over at Amazon. Today, you can buy the Keurig K-Mini Single Serve Coffee Maker for just $50 saving 50% off the regular price of $100. One of the best Keurig deals for those who like to enjoy a cup of ...

One of our favorite early Black Friday deals is 50% off this Keurig

a pour-over coffee maker and filters, to start, and just as importantly, a gooseneck electric kettle. Featuring an elongated, thin and curvy spout, gooseneck kettles give you ultimate control over your water flow and direction, allowing you to precisely ...

Fellow Stagg EKG Electric Kettle Review: A Sleek Pick For Pour-Over Coffee

Eyewear retailer Lenskart has acquired TangoEye, an AI-based computer vision startup. With this acquisition, lenskart plans to use visual Artificial Intelligence technology in improving store experience as well as its product experience, a statement said.

Startup Digest: Lenskart acquires TangoEye, Skyroot Aerospace raises $27.5 Mn, Apple India’s profits up 77% in FY23 & more

Filter Coffee

filtercoffee

No, seriously, why not? It’s cool to be a designer, a creative person who DJs vinyl sets in a funky club at night and starts the morning with a double-filter coffee and a vegan croissant overlooking the sea to get inspired for the next advertising campaign. Not a bad stereotype, right? A brand designer is a visual communicator with the user. The same user who has to pull money out of their pocket and give it to the founder for using their SaaS product. It’s a very interesting task, isn’t it? That’s why I think it would be very useful for a tech founder/CEO to possess design skills to learn a couple of extremely important competencies that will help their business grow steadily. Creativity and Innovation Unfortunately, internet culture and ignorant people in pursuit of money have desecrated these concepts, making them clichés. This is a shame because creativity and innovation are two of the main drivers of progress, which are linked by a simple logic: creativity is the ability to generate new ideas, approaches, and concepts that help solve existing problems in ways that did not exist before. is the process of transforming new ideas or concepts into real products, services, or processes. It’s very simple and complex at the same time. Innovation and the ability to are the main tools of a designer. Let me give you a simple example: a large IT company approached us to create an identity for a low-code platform brand, which was meant to be an alternative to expensive full-code developers in large enterprise projects. But in the course of our research, we found that the challenge is not competition or usability, but the presence of so-called “citizen developers” — marketers, accountants, HR specialists who know how to create software using low-code platforms. Instead of highlighting the product’s advantages, we offered to sell the product along with a course on low-code for employees of different departments to help them become more efficient. Now it’s not just a product, but a new hard skill for the next generation of employees. This is how designers’ creativity leads to business innovation. Creativity innovate Knowledge and Understanding of Branding Some tech founders make the mistake of thinking that a brand is a logo and colors created in 15 minutes with the help of the first service in Google search results based on artificial intelligence. is, of course, top of the line, but it is more of a designer’s assistant than a full-fledged founder’s vendor. AI A brand is a set of associations, emotions, and feelings associated with a product. Spotify is a cool selection of music and a playlist of the week’s discoveries. — art, fast, magic, prompt. — bitcoin, trading, reliable. These are my very first associations with the products of these brands. Pause for 1 minute and think about the tech products you use every day. What associations and emotions do they evoke in you? These are brand assets. Midjourney Binance Brand assets help to form a unique brand identity that will help it stand out from the competition and take its place in the market in the user’s mind. Positioning, corporate visual identity, website design, the style of music in a commercial, and even the way your sales managers communicate with customers — all of these are tech brand assets, not just the logo, colors, and name. For example, there’s a recent Tik Tok campaign for the UK. It’s a good example of how a brand is trying to take a bite out of Google’s core brand asset “the place where I go if I need some information.” And this is branding too, yes 🙂 The Semiotic Triangle Principle What is the difference between art and design? Design necessarily has a function, whereas art has only meaning and form. That is, design is visual communication based on a combination of meaning, form, and function. And when working with your tech brand, it is very important to interpret “function” as a question: “who is this for?”, “why are we doing this?”, “what do we want to achieve with this design?”, etc. And this is where a huge problem for young tech startups opens up because when I received briefs on brand identity development from founders, I noticed that 80% of them do not pay attention to user research at all. “Our audience is marketers.” Are you sure your product can help every marketer? Or maybe just a digital marketer? Or maybe only a digital marketer of a DTC brand? Or maybe only a digital marketer of a DTC brand who does their job on experience but pays for seven different marketing services just to justify their luck to the CEO at the end of the project? A thorough understanding of the brand’s audience, communication goals, and desired outcome is all about “function” from the same semiotic triangle that we, as designers, keep in our pockets at all times. How can a startup founder use a designer's approach? Reface, a Ukrainian startup that creates an app with face swap videos, prepared a funny one-minute video featuring the most popular video memes for each pitch, showing the product in action. What's so creative about it? All the faces from the memes were replaced with those of investors, who were simply delighted. As a result, they raised 5.5M in investments. Use creativity as a tool to solve your business problems. A few years ago, Tinder began to feel that competitors were breathing down its neck, and the user base was not growing. After speaking with users, they discovered that millennials (aged 25-35) were no longer using the app because it didn't help them find more serious relationships. In response, Tinder upgraded its features with "Tinder Explore," which allows users to sort matches by interests, and launched the "You're Not For Everyone" ad campaign. These changes resulted in an 8% uplift over the campaign period and an increase in revenue of $17M quarter-on-quarter. Stay in constant touch with users and test new product ideas and features with them. Mailchimp was not bought for the monkey on the logo; rather, it was purchased for taking the monkey off the entrepreneur's back and offering to do business--not operations--by sending thousands of emails automatically. Perceive the brand as an emotional and associative connection between the product and the user. meaning (what am I doing?), form (how do I do it?), and function (why am I doing it?). Design business tasks according to the semiotic triangle principle: By the way, the founders of Instagram, YouTube, Canva, and Airbnb are good examples of billion-dollar companies with designer founders, and it's likely that their design skills contributed to their success.

Tech founder, Why Haven't You Become a Designer Yet?

There is no doubt that the world's workforce is becoming more remote, particularly in tech as developers can now work from any location in the world. But there are a large number of new obstacles that come with this. The most pressing is security. Take the current COVID-19 health crisis. From one day to the next, countries are going into quarantine and forcing companies  and developers into working remotely. I for one am writing this from my home office in Paris, sipping filter coffee while looking onto the empty streets in a complete lock-down that started last week. We are all trying to rapidly adjust to a new workflow from our makeshift workstations. As this crisis unfolds, we are seeing a drastic change in the remote infrastructure as it is pushed to its limit. This is creating an unprecedented security challenge that may not be a priority for many companies. I recently got an alert to say that Microsoft Teams, a platform to help manage remote workers and collaboration, crashed under the new pressure of millions of users. Likewise the video streaming company Zoom, which is now facing serious security breaches, went from 10 million daily users in December to 200 million daily users in March! These are obvious signs of the strain we are putting on digital infrastructure as this crisis unfolds. Teams that are only  familiar with being centralized on a secure network, are now at home on a private WiFi network trying their best to keep websites, services, platforms and apps functioning. Image taken from Downdetecter, 16 March 2020 Why does remote work present a security risk? Remote working is not necessarily less secure, if done right.  It is  extremely important for remote teams to have operational frameworks and tools in place to deal with the challenges that are unique to remote teams. Especially when it comes to issues as urgent as security. If you make secret management too painful for developers, it becomes all too tempting to circumvent the restrictions. Now let's add to this balancing act different geographies, time zones and unreachable co-workers. The temptation to send a secret over Slack, email or to quickly hardcode secrets into the source code becomes increasingly hard to resist. In addition to internal operational control, we must now also consider external factors, such as unsecured personal wifi-networks and personal machines. These all add to the potential of secrets being leaked into unsecured locations. The once clear delineation between personal and professional activity suddenly becomes blurred when you are sharing a single machine and mixing personal git repositories with professional repositories. You can easily find yourself in the worst case (yet common) scenario of mistakenly uploading secrets and corporate code into personal repositories. All these factors combined increases the attackable area of the organization, when secrets that were once secured to a central location are now sprawled across multiple platforms and tools. Unsecured messaging systems like Slack and email are known to be high-value targets for attackers through phishing campaigns. Once an attacker has admin access to your company's Slack, they are a simple search away from gaining access to your sensitive information. The normal conditions that could help identify an external threat also begin to unravel, as IP addresses of all developers can change daily. Currently, the number of reported phishing campaigns has drastically increased with the COVID-19 crisis unfolding as attackers piggy-back onto public panic. 8 steps to making remote work more secure for development teams 1. Get visibility over secrets shared in your repositories Repositories are not appropriate places to store secrets, it is important that you have complete visibility of not only public repositories but also of your private repositories. Having secrets in private repositories:; increases the chances of a secret ending up on a public repository. Perhaps more importantly though, now that employees are increasingly working from home, it is more likely company code will end up on unsecured personal workstations or be transiting through unsecured networks, meaning secrets within that source code could be accessible by a malevolent actor. We recommend signing up to to scan your repositories. The tool is free for small teams and will allow you to scan your git history for secrets while continuously scanning incremental changes as well. GitGuardians Internal Repository Monitoring 2. Establish best practices sharing sensitive information For a helpful guide on best practices for securing and sharing API keys, you can check out a GitHub . It’s a great document to share with your staff to make sure they are using the best practices. cheatsheet here 3. Know how to remediate compromised credentials Make sure the steps on how to revoke a credential for each external service is documented along with the impact revoking each credentials will have. You can find . here an article about revoking and removing compromised credentials 4. Invest in multi-factor authentication on all services 2 factor authentication improves security drastically when it comes to services and assets you want to protect. Make sure 2FA is set up on all “Seed” accounts like company emails. (A seed account is an account that can grant access to other services. Email and social media accounts are examples of seed accounts, “log in with Gmail”). 5. Work towards implementing a Zero Trust framework Depending on the organization it may be appropriate to implement a Zero Trust framework beyond multi-factor authentication. Complete implementation is always a process and a considerable investment but introducing additional Zero Trust measures like SSO and introducing policies to block/limit/approve access decisions to enterprise resources can strengthen security significantly, particularly for remote teams. 6. Encourage a culture of self education By providing clear and basic information, including how to protect their devices, will help you and employees stay ahead of threats. Remote workers have access to data, information, and your network. This increases the temptation for bad actors. Warn your employees to expect more phishing attempts, including targeted spear phishing aimed at high profile credentials. Now is a good time to be diligent, so watch out for urgent requests that break company policy, use emotive language and have details that are slightly wrong and provide guidance on where to report those suspicious messages. 7. Practice good security hygiene Good security practices always go back to basics. Practicing good security hygiene means to rotate vulnerable access credentials regularly and to restrict permissions associated with your keys with role-based access controls. 8. Be prepared for a breach Regardless of whether your team is working remotely or not, it is important to have an action plan in place in case secrets get shared. Make sure the whole team understands what that plan is and implement a culture of open, transparent communication. We are certainly facing difficult times that are bringing new challenges at record speeds. It is unfortunate that while we are adapting, there are people out there that are using this as an opportunity to exploit us. GitGuardian is, and always will be free for developers and small teams so if you are working with a remote team, consider implementing the free tools GirGuardian have to allow you to continue thriving during the times ahead. 👉 Try GitGuardian | automated secret detection Free 0$/month Monitor your public activity anywhere on GitHub Real-time alerting by email _article originally published on GitGuardian's blog: 8 Steps to keep remote development teams secure

8 Basic Steps to Keep Your Remote Development Teams Secure

Since about a week, I have been reading about Turing machine and slowly extending the concept to the world of . Primarily, the Ethereum blockchain. After gaining enough insights into the subject, I decided to give voice to my thoughts. blockchain Let me delineate the scope of this article so that you know what you’re in for. I’ll start off by giving a gentle introduction to the Turing machine followed by a discussion on the Church-Turing thesis. I’ll touch the Chomsky hierarchy. (Just a bit) I’ll discuss the fundamentals of an I/O Turing Machine by taking an example, accompanied with some basic mathematics. After this, we will see how the Ethereum blockchain is not completely Turing complete and why it is rudimentary in the present-day. We will see what implications Turing completeness can have on the Ethereum blockchain with respect to the concept of Ethereum gas. We will go over the fundamentals of the Vyper language. We will then see in what scenarios a true Turing machine can transform smart contracts (this section contains futuristic talk). I’ll try to keep my language as simple as possible, for obvious reasons. This article is best enjoyed with a cup of strong filter coffee. If you don’t know what is filter coffee then you are missing out on life, my friend. Alan M. Turing and his Turing Machine The idea of Turing machine was conceived about 83 years ago, in 1936, by an English computer-scientist named Alan M. Turing. It was the time when the British empire was in its prime, so Turing had part of his early education in India. All of Turing’s contributions were not respected in the UK due to the fact that he was homosexual even though he broke the ‘unbreakable’ German Enigma code during WW II. He rose to intellectual fame becoming founder of computer science and artificial intelligence only after his death. Turing died anonymous, penniless, and impotent. Such is life? The Turing machine is a theoretical, abstract idea which can simulate any algorithm that can be logically constructed. This means that a Turing machine can solve problem if it can be coded out. There’s special emphasis on the word . It certainly doesn’t guarantee how much time it will take to solve the problem, but it is certain that it will solve the problem. So it can take a second, a minute, a lifetime, or an entire generation to solve a problem. any any But don’t you worry, your problem will be solved sooner or later! Construction of a Turing Machine Take an infinitely long tape (the length of the tape depends upon the length of the input, expected output, and complexity of the problem to be solved). The tape is divided into discrete cells. There would be infinite cells on the tape. The machine has a pointer, or a head which is capable of maneuvering left or right on the tape. A head also has the ability to erase data on the cell and write new data. Everything that is to be deleted from the tape and everything that is to be added to the tape is governed by user-described instructions. When a Turing machine starts, anything written on the tape is the input. When the Turing machine halts after completion of the problem, anything written on the tape is the output. To compute the solution for a given problem, the Turing machine may read and write on the tape for infinite amount of time. There is no boundary on any resource like time, memory whatsoever. Each cell has an associated state with it which is governed by user-described instructions. The state of the cell changes when an event occurs. When state changes, the value on the cell may be rewritten. The mappings on the Turing machine are as follows: Where, is a function. 𝛿 is the current state q is the value on the cell a is the new state P is the new value on the cell A means that the head will move right R would mean that the head would move left L Church-Turing thesis or Computability thesis This thesis says that whatever that can be called an effective procedure can be realized by a Turing machine. A procedure is slightly different than an algorithm. An algorithm always comes to a halting state sooner or later. A procedure may or may not halt, it can go on forever. There are some functions which can’t even be calculated by a Turing machine. For instance, quantum computers can simulate procedures in less amount of time as compared to modern computers. In contrast, there exist problems such as halting problem which an ordinary computer cannot answer. And according to Church-Turing thesis, no computational device can answer such a question. In , the is the problem of determining, from a description of an arbitrary and an input, whether the program will finish running (i.e., halt) or continue to run forever. computability theory halting problem computer program Source: Wikipedia. Chomsky Hierarchy According to Chomsky, there are 4 types of languages which are differentiated based on the freedom of usability. Type 0 is the most flexible, free language and Type 3 is the most restricted language. The language used in a Turing machine falls under Type 0 classification because the programmer has all the flexibility in the world to write whatever program they wish to write. Most of the languages today are Turing complete or Type 0 languages like C++, Java, JavaScript, Solidty, Pascal etc. Almost all languages today are Turing complete. The only languages that are not Turing complete are markup languages. Turing machine as an I/O device Let’s solve a problem on the Turing machine. Check if the number of 1s in the string is even or odd. Problem statement: Let’s take an infinitely long tape with a random string on it. Let the string be . The other cells are blank. 0 1 0 1 1 0 The Turing machine should print ‘o’ if the number of 1s is odd and should print ‘e’ if the number of 1s is even. The rules governing machine behavior are as follows: The initial state of the machine is q0. Since the head is on the start of the string, the mapping would be as follows: The Turing machine will look like this now: This is because the data on the first cell was 0. The state will change only when the machine head will encounter 1. Next mapping: The Turing machine will look like this now: Next mapping: The Turing machine will look like this now: Next mapping: The Turing machine will look like this now: Next mapping: The Turing machine will look like this now: Next mapping: The Turing machine will look like this now: Next mapping: H is the halting state. This means the machine has stopped since it encountered a blank. The head won’t move left or right so there is a — . The Turing machine will look like this now: Since the machine halted at q1 state, there are odd number of 1s. If the machine halted at q0 state, there would be even number of 1s. Note that ‘o’ is printed on the first encountered blank. This is how a Turing machine is an I/O device. I hope this gave you a gist of what a Turing machine really is. In the next section, we will explore the consequences of Turing machine with respect to the Ethereum blockchain. Let’s see how the Ethereum blockchain is NOT actually Turing complete. For those who don’t know the concept of in Ethereum. This will be used a lot in the article. Here’s a basic definition: Gas Gas is the unit of measurement used to measure cost of running an operation on the Ethereum blockchain. The concept of gas exists to separate computational cost of running an operation (opcode) from market value of Ethereum (which is currently highly volatile). So the cost to add 2 numbers in gas will always be the same even if 1 ETH = 10,000 USD or 1 ETH = 1 USD. Each block on the Ethereum blockchain has . This is the maximum amount of gas that can be spent. Block gas limit On 14th Feb 2019, 3:10 PM, the gas limit was 8000029 gas and the gas price was 5.1 gwei. This information can be easily found at . It’s a really cool site. ethstats.net Such limits are imposed to protect the blockchain from DDoS attacks. If you want to translate gas spent per unit time, you can check out for the conversion. ethgasstation.info So effectively, there are 2 limits imposed on any I/O on the Ethereum blockchain. These are gas cost and block gas limit. And, we know that a true Turing machine works with unlimited resources and the concept of Gas prevents the Ethereum blockchain to be truly Turing complete. And that’s perfectly alright! Because… Today, we do not really need Turing completeness due to the fact that present day smart contracts are very mundane. They solve really basic stuff like transferring funds, changing ownership, maybe adding two numbers. That’s all! Really. Nothing fancy is being done here. I’ll tell you what fancy is. Fancy is: 1. Powering solar panel using electricity purchased from a decentralized grid to power your water heater. 2. Your smart car interacts with other smart cars on the road and when your car pays the other smart car in crypto, those cars will clear the way for you. 3. Your smart car will not ignite if you consume too much alcohol. Alcohol consumption can be recorded using smart sensors. Let your imagination run wild. The things you would imagine; they are classified as fancy. This stuff is fancy because it deals with autonomous intelligence. For such things, we need very sophisticated smart contracts which will run for a higher gas than the current block gas limit. This is the future. The future is not P2P, rather it is M2M. This is where we need Turing completeness. Although, with great power comes great responsibility! Solidity, the Turing complete language for Ethereum smart contract, gives a lot of flexibility to the smart contract developer. A smart contract can only be deployed once on the blockchain. If anything minor goes wrong, the consequences can be drastically catastrophic. A hacker can run any algorithm on EVM written in solidity to create an unauthorized effect. Most of it will be prevented due to the gas limit, but still, the damage cannot be undone. Of course, running arbitrary code is dangerous! For instance, there can be a buffer overflow in a banking application that allows the hacker to change their balance. This kind of vulnerability arises due to the fact that Solidity gave the hacker such freedom. The current ERC20 token cannot securely and cheaply handle hyper-efficient, high throughput processing for carrying out sophisticated M2M interactions devoid of human intervention and oversight. This is why some folks say that Solidity is not the right choice for the smart contract. We need something more secure, auditable. This is where Vyper comes in. A basic example of this would be to store health data on Ethereum blockchain. Solidity may not be the best language for that. Vyper surely is. If you want to create super-secure smart contracts, go for Vyper. Don’t get me wrong, the language Solidity is Turing complete but the “world-computer” EVM is not. The language Vyper is NOT Turing complete, Solidity is. At the same time, a program written in Vyper will always have a predictable output. A program written in Solidity will not have a predictable output until and unless it is deployed and executed. In Vyper, simplicity and readability of the language by a lay-man is more important than freedom and flexibility of Turing complete languages. Vyper doesn’t support the following features: 1. Modifiers 2. Class inheritance 3. Inline assembly 4. Function overloading 5. Operator overloading 6. Recursive calling 7. Infinite-length loops 8. Binary fixed point A fun fact, in Python 0.3 + 0.3 + 0,3 + 0.1 is NOT equal to 1. Coming back… So, there’s a tradeoff between security and freedom here. Fewer features, more auditability, and more security. More features, less auditability and less security. Developers of Vyper say it themselves — Will deliberately forbid things or make things harder if it deems fit to do so for the goal of increasing security. Don’t get me wrong. Vyper was NOT created to replace Solidity. It was created to boost security. A recent study found more than 3000 vulnerable contracts contain security flaws. Since we are talking about blockchain, let’s take a while to remember the blockchain. The Bitcoin scripting language is NOT Turing complete. The script only performs one function: transfer funds from one account to another. So there is no need for Turing completeness. So the behavior space of bitcoin script is predictable, as opposed to Solidity. Bitcoin I’d like to take this final opportunity to re-emphasise on the future of smart contracts. Some of us might call autonomous machines interacting with each other ‘too fancy’. But I kid you not, probably by the time you are a grandparent, you’d be surprised to see that this, in fact, will become a reality. Do you want to be one of those old people who don’t understand technology anymore? I am guessing the answer here is no. So, don’t tag such ideas as ‘fancy’ and start contributing to these futuristic thoughts! Who cares if we sound crazy? If crazy people love to ideate, call me crazy! That’s enough of me ranting there, lol. In the next article, I’d probably show you how to write smart contracts in Vyper language. I really hope you enjoyed this. If you did, please applaud and support these ‘crazy’ ideas!

Turing Completeness and the Ethereum Blockchain

Unexpected SQL Server Performance Killers #2 In this series I explore scenarios that hurt SQL Server performance and show you how to avoid them. Pulled from my collection of “things I didn’t know I was doing wrong for years.” Prefer video? Watch this post on YouTube. Last week we discussed how . implicit conversions could be one reason why your meticulously designed indexes aren’t getting used Today let’s look at another reason: sniffing. parameter Here’s the key: Parameter sniffing isn’t always a bad thing. Most of the time it’s good: it means SQL Server is caching and reusing query plans to make your queries run faster. Parameter sniffing only becomes a problem when the cached plan isn’t anywhere close to being the optimal plan for given input parameters. So what’s parameter sniffing? Let’s start with our table dbo.CoffeeInventory which you can . grab from Github The key things to know about this table are that: We have a nonclustered index on our Name column. The data is not distributed evenly (we’ll see this in a minute) Now, let’s write a stored procedure that will return a filtered list of coffees in our table, based on the country. Since there is no specific Country column, we’ll write it so it filters on the Name column: DROP PROCEDURE IF EXISTS dbo.FilterCoffeeGOCREATE PROCEDURE dbo.FilterCoffee varchar(30)ASBEGINSELECT Name, Price, DescriptionFROM Sandbox.dbo.CoffeeInventoryWHERE Name LIKE + '%'ENDGO @ParmCountry @ParmCountry Let’s take a look at parameter sniffing in action, then we’ll take a look at why it happens and how to solve it. EXEC dbo.FilterCoffee @ParmCountry = 'Costa Rica'EXEC dbo.FilterCoffee @ParmCountry = 'Ethiopia' Running the above statement gives us identical execution plans using table scans: In this case we explicitly specified the parameter @ParmCountry. Sometimes SQL will parameterize simple queries on its own. . We have two query executions, they are using the same plan, and neither plan is using our nonclustered index on Name! That’s weird Let’s step back and try again. First, clear the query plan cache for this stored procedure: DECLARE varbinary(44)SELECT = c.plan_handleFROMsys.dm_exec_cached_plans cCROSS APPLY sys.dm_exec_sql_text(c.plan_handle) tWHEREtext like 'CREATE%CoffeeInventory%'-- Never run DBCC FREEPROCCACHE without a parameter in production unless you want to lose all of your cached plans...DBCC FREEPROCCACHE( ) @cache_plan_handle @cache_plan_handle @cache_plan_handle Next, execute the same stored procedure with the same parameter values, but this time with the ‘Ethiopia’ parameter value first. Look at the execution plan: EXEC dbo.FilterCoffee @ParmCountry = 'Ethiopia'EXEC dbo.FilterCoffee @ParmCountry = 'Costa Rica' Now our nonclustered index on Name is being utilized. Both queries are still receiving the same (albeit different) plan. We didn’t change anything with our stored procedure code, only the order that we executed the query with different parameters. What the heck is going on here!? This is an example of parameter sniffing. The first time a stored procedure (or query) is ran on SQL server, SQL will generate an execution plan for it and store that plan in the query plan cache: SELECTc.usecounts,c.cacheobjtype,c.objtype,c.plan_handle,c.size_in_bytes,d.name,t.text,p.query_planFROMsys.dm_exec_cached_plans cCROSS APPLY sys.dm_exec_sql_text(c.plan_handle) tCROSS APPLY sys.dm_exec_query_plan(c.plan_handle) pINNER JOIN sys.databases dON t.dbid = d.database_idWHEREtext like 'CREATE%CoffeeInventory%' All subsequent executions of that same query will go to the query cache to reuse that same initial query plan — this saves SQL Server time from having to regenerate a new query plan. Note: A query with different values passed as parameters still counts as the “same query” in the eyes of SQL Server. In the case of the examples above, the first time the query was executed was with the parameter for “Costa Rica”. Remember when I said this dataset was heavily skewed? Let’s look at some counts: SELECTLEFT(Name,CHARINDEX(' ',Name)) AS Country,COUNT(*) AS CountryCountFROM dbo.CoffeeInventoryGROUP BYLEFT(Name,CHARINDEX(' ',Name)) “Costa Rica” has more than 10,000 rows in this table, while all other country names are in the single digits. This means that when we executed our stored procedure for the first time, SQL Server generated an execution plan that used a table scan because it thought this would be the most efficient way to retrieve 10,003 of the 10,052 rows. This table scan query plan is only optimal for Costa Rica . Passing in any other country name into the stored procedure would return only a handful of records, making it more efficient for SQL Server to use our nonclustered index. , since the Costa Rica plan was the first one to run, and therefore is the one that got added to the query plan cache, all other executions ended up using the same table scan execution plan. However After clearing our cached execution plan using , we executed our stored procedure again but with ‘Ethiopia’ as our parameter. SQL Server determined that a plan with an index seek is optimal to retrieve only 6 of the 10,052 rows in the table. It then cached that Index Seek plan, which is why the second time around the ‘Costa Rica’ parameter received the execution plan with Index Seek. DBCC FREEPROCCACHE Ok, so how do I prevent parameter sniffing? This question should really be rephrased as “how do I prevent SQL Server from using a sub-optimal plan from the query plan cache?” Let’s take a look at some of the techniques. 1. Use WITH RECOMPILE or OPTION (RECOMPILE) We can simply add these query hints to either our EXEC statement: EXEC dbo.FilterCoffee @ParmCountry = 'Ethiopia' WITH RECOMPILEEXEC dbo.FilterCoffee @ParmCountry = 'Costa Rica' WITH RECOMPILE or to our stored procedure itself: DROP PROCEDURE IF EXISTS dbo.FilterCoffeeGOCREATE PROCEDURE dbo.FilterCoffee varchar(30)ASBEGINSELECT Name, Price, DescriptionFROM Sandbox.dbo.CoffeeInventoryWHERE Name LIKE + '%' @ParmCountry @ParmCountry OPTION (RECOMPILE) ENDGO What the RECOMPILE hint does is force SQL Server to generate a new execution plan every time these queries run. Using RECOMPILE eliminates our parameter sniffing problem because SQL Server will regenerate the query plan every single time we execute the query. The disadvantage here is that we lose all benefit from having SQL Server save CPU cycles by caching execution plans. If your parameter sniffed query is getting ran frequently, RECOMPILE is probably a bad idea because you will encounter a lot of overheard to generate the query plan regularly. If your parameter sniffed query doesn’t get ran often, or if the query doesn’t run often enough to stay in the query plan cache anyway, then RECOMPILE is a good solution. 2. Use the OPTIMIZE FOR query hint Another option we have is to add either one of the following hints to our query. One of these would get added to the same location as OPTION (RECOMPILE) did in the above stored procedure: OPTION (OPTIMIZE FOR (@ParmCountry UNKNOWN)) or OPTION (OPTIMIZE FOR (@ParmCountry = 'Ethiopia')) OPTIMIZE FOR UNKNOWN will use a query plan that’s generated from the average distribution stats for that column/index. Often times it results in an average or bad execution plan so I don’t like using it. OPTIMIZE FOR VALUE creates a plan using whatever parameter value specified. This is great if you know your queries will be retrieving data that’s optimized for the value you specified most of the time. In our examples above, if we know the value ‘Costa Rica’ is rarely queried, we might optimize for index seeks. Most queries will then run the optimal cached query plan and we’ll only take a hit when ‘Costa Rica’ is queried. 3. IF/ELSE This solution allows for ultimate flexibility. Basically, you create different stored procedures that are optimized for different values. Those stored procedures have their plans cached, and then an IF/ELSE statement determines which procedure to run for a passed in parameter: DROP PROCEDURE IF EXISTS dbo.FilterCoffeeGOCREATE PROCEDURE dbo.FilterCoffee varchar(30)ASBEGINIF = 'Costa Rica'BEGINEXEC dbo.ScanningStoredProcedure @ParmCountryENDELSEBEGINEXEC dbo.SeekingStoredProcedure @ParmCountryENDENDGO @ParmCountry @ParmCountry This option is more work (How do you determine what the IF condition should be? What happens more data is added to the table over time and the distribution of data changes?) but will give you the best performance if you want your plans to be cached and be optimal for the data getting passed in. Conclusion Parameter sniffing is only bad when your data values are unevenly distributed and cached query plans are not optimal for all values. SQL Server caches the query plan that is generated from the first run of a query/stored procedure with whatever parameter values were used during that first run. Using the RECOMPILE hint is a good solution when your queries aren’t getting ran often or aren’t staying in the the query cache most of the time anyway. The OPTIMIZE FOR hint is good to use when you can specify a value that will generate a query plan that is efficient for most parameter values and are OK with taking a hit for a sub-optimal plan on infrequently queried values. Using complex logic (like IF/ELSE) will give you ultimate flexibility and performance, but will also be the worst for long term maintenance. Still reading? You’ll probably enjoy following me on Twitte r

Stories, News & Mentions about FILTERCOFFEE

Categories

Trending Topics

Stories, News & Mentions about FILTERCOFFEE

Filtercoffee’s Stories on HackerNoon

Filtercoffee's News & Mentions

Categories

Trending Topics